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...
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.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
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