Conditional Lookup

somu_june · Post by **somu_june** » Tue Dec 20, 2005 7:12 am

Hi Guys,

I believe lot of people have faced this problem. I have source in flat file. I need to lookup to a flat file which has ranges .
eg: source column, number
Lookup file, low_num,high_num, groupid

I have to lookup to this file and if my source number falls in one of the groups I need to get the groupid in the output.

Any suggestions are appreciated.

-Somu

ml · Post by ml » Tue Dec 20, 2005 7:25 am

If you can create a job to load the hash file lookup with the columns ID_NUM<key> and GROUP, then in your job you should be able to join the ID_NUM and get the GROUP number.

sounds easy, good luck

somu_june · Post by **somu_june** » Tue Dec 20, 2005 8:00 am

hey ml,

what should I be loading into hashed file ? low_num,high_num & group..what is id_num ?

-somu

ml · Post by ml » Tue Dec 20, 2005 8:20 am

Oh, I think that now I understand your requirement. I believe that there is no Conditional Lookup stage (you need parallel Lookup stage for that).

If you only have two or three groups you can just create a derivation in a transformer stage like "If ID > Low then "group1" else "group2"". If you need something more complex you can serch "Conditional Lookup" in this forum.

God luck

chulett · Post by **chulett** » Tue Dec 20, 2005 8:36 am

You actually want a Range lookup, not a conditional one. Search the forum for the various discussions on that topic.

kduke · Post by **kduke** » Tue Dec 20, 2005 5:44 pm

Craig is correct. You need to build the hash file with some arbitrary key. You need to use a UV stage unless you can find and build multivalues. Save your metadata from the hash file load. Leave the hash file in the account (no path and no account name) unless you want to figure out SETFILE.

Next using the UV stage as an lookup, load the metadata saved from the hash file. Change the key to be the 2 fields you want to lookup. Change the SQL to user defined. Change the SQL to be a between join.

Please no private messages.

ArndW · Post by **ArndW** » Wed Dec 21, 2005 2:16 am

Kim - thanks for that! I vaguely recalled that there was something out there, but a couple of searches through the VOC for typical keywords didn't return any results so I though my memory had played a trick on me.