Range Lookup

yinyin61 · Post by **yinyin61** » Wed Feb 28, 2007 1:58 am

hi,

i'm facing problem of doing range lookup for hash file. below is my source and lookup data

Source
ID Rng
1 156

Lookup Data
ID Rng1 Rng2
1 100 150
1 151 200
2 100 200

I need to lookup the ID and the Rng should be between Rng1 and Rng2.

Both the Source and Lookup data are .csv file. I had loaded Lookup Data into hash file with ID as key but then i realize that the record will be overwrite by the next record and results only ID 1, Rng1 151, Rng2 200 in the hash file.

So anyone can give me suggestion what can i solve this problem? Thanks in advance...

Edwink · Post by **Edwink** » Wed Feb 28, 2007 2:52 am

hope merge stage can slove this issue

yinyin61 · Post by **yinyin61** » Wed Feb 28, 2007 3:32 am

Edwink wrote:hope merge stage can slove this issue

thanks for the reply

i guess i know how to do already..

kumar_s · Post by **kumar_s** » Wed Feb 28, 2007 7:50 am

Yes, there could be several method to solve this. Do you mind sharing the method you have chosen. So that it can be useful for further searchers, somtimes you may bet better advise as well.
And you can mark the topic as resolved as well.

yinyin61 · Post by **yinyin61** » Wed Feb 28, 2007 8:01 pm

kumar_s wrote:Yes, there could be several method to solve this. Do you mind sharing the method you have chosen. So that it can be useful for further searchers, somtimes you may bet better advise as well.
And you can mark the topic as resolved as well.

of course i don't mind sharing my method

i use merge stage to merge Source and Lookup Data (key is Source.ID = LookupData.ID) then i use a transformer stage to do the range lookup. I add a stage variable in transfomer with the below derivation:

If Source.Rng >= LookupData.Rng1 And Source.Rng <= LookupData.Rng2 Then @TRUE Else @FALSE

This above stage variable will act as a constraint.

Welcome any better idea to solve this

ray.wurlod · Post by **ray.wurlod** » Wed Feb 28, 2007 8:15 pm

The stage variable could have been more simply derived as

Code: Select all

 Source.Rng >= LookupData.Rng1 And Source.Rng <= LookupData.Rng2

If expression Then @TRUE Else @FALSE is redundant because expression itself must return a true/false result. In DataStage BASIC, 0 or "" (or anything that is equivalent) is regarded as False, and any other non-null value is regarded as True.

yinyin61 · Post by **yinyin61** » Wed Feb 28, 2007 8:48 pm

ray.wurlod wrote:The stage variable could have been more simply derived as
Code: Select all
 Source.Rng >= LookupData.Rng1 And Source.Rng <= LookupData.Rng2 
If expression Then @TRUE Else @FALSE is redundant becau ...

mm... i dont have the premium membership so not able to read your reply

kumar_s · Post by **kumar_s** » Wed Feb 28, 2007 9:00 pm

But its easy to get the membership. By that you can help Dsxchange to help you.

yinyin61 · Post by **yinyin61** » Wed Feb 28, 2007 9:08 pm

kumar_s wrote:But its easy to get the membership. By that you can help Dsxchange to help you. ...

need to pay USD99 a yr in order to get the membership...

kumar_s · Post by **kumar_s** » Wed Feb 28, 2007 9:18 pm

May be less than 3cents a day. Sometimes you may find it more helpful for your professional life to get a quick solution.

yinyin61 · Post by **yinyin61** » Wed Feb 28, 2007 9:34 pm

kumar_s wrote:May be less than 3cents a day. Sometimes you may find it more helpful for your professional life to get a quick solution. ...

i'll think about it, thanks

ray.wurlod · Post by **ray.wurlod** » Thu Mar 01, 2007 12:18 am

Corporate discounts are available. There's a link from the DSXchange home page to information about these. Premium membership money is entirely consumed helping to pay for the bandwidth costs of DSXchange.

yinyin61 · Post by **yinyin61** » Thu Mar 01, 2007 12:41 am

ray.wurlod wrote:Corporate discounts are available. There's a link from the DSXchange home page to information about these. Premium membership money is entirely consumed helping to pay for the bandwidth costs of DSXchange.

yup, i will ask my company whether can register the premium membership for me or not