Range Lookup
Moderators: chulett, rschirm, roy
Range Lookup
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...
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...
Thank you.
Regards,
Aileen Chong
Software Engineer
Regards,
Aileen Chong
Software Engineer
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.
And you can mark the topic as resolved as well.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
of course i don't mind sharing my methodkumar_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.
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
Thank you.
Regards,
Aileen Chong
Software Engineer
Regards,
Aileen Chong
Software Engineer
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Code: Select all
Source.Rng >= LookupData.Rng1 And Source.Rng <= LookupData.Rng2
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
mm... i dont have the premium membership so not able to read your replyray.wurlod wrote:The stage variable could have been more simply derived asIf expression Then @TRUE Else @FALSE is redundant becau ...Code: Select all
Source.Rng >= LookupData.Rng1 And Source.Rng <= LookupData.Rng2
Thank you.
Regards,
Aileen Chong
Software Engineer
Regards,
Aileen Chong
Software Engineer
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
yup, i will ask my company whether can register the premium membership for me or notray.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.
Thank you.
Regards,
Aileen Chong
Software Engineer
Regards,
Aileen Chong
Software Engineer