DSXchange

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...

hope merge stage can slove this issue

Edwink wrote:hope merge stage can slove this issue

thanks for the reply

i guess i know how to do already..

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.

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

The stage variable could have been more simply derived as 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.

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

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

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...

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

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 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