I have an input link, Link1, that has two values coming in such as:
Val1 Val2
10 20
90 100
200 100000
etc.
There are some situations where Val2 might have a value of 100000.
From Link2, I have values coming in ranges such as:
ValX ValY ValCol
30 40 A
70 80 B
110 120 C
150 160 D
250 300 E
etc.
If Val2 <> 100000 then I need to use Val2 to find the next higher range of ValX and ValY to get the corresponding ValCol value. So for Val1/Val2 value of 10,20, the ValCol value to find would be A. For Val1/Val2 of 90,100 the ValCol value to find would be C and so on. If Val2 is 100000 then I need to use Val1 to find the next higher range which would be 250,300 and the corresponding ValCol to find would be E.
Any ideas how I can implement this?
Looking for design suggestions
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You can split the data into two streams, one where Val2 is 100000 and the other where it is not. Have a Lookup stage on each, the one using Val1 as the key, the other using Val2 as the key. Bring the two streams back together using a Funnel stage.
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.
-
- Premium Member
- Posts: 425
- Joined: Sat Nov 19, 2005 9:26 am
- Location: New York City
- Contact:
How many items in the list of ValX ValY ValCol ?
If small number then one way to implement it is using a transformer/loop. It will be a matter of aggregating the values into a pipe separated list of ValX,ValY,ValCol | ValX,ValY,ValCol| ... This pipe list that will be your looping values
In a transformer, for each Val1, Val2 pair will be evaluated against the looping list created above to get get the ValCol and exit out the loop once you get a match...process the next pair. In the loop you would need to parse out the ValX, ValY and ValCol to use it as below
If ValX < Val2 < ValY then ValCol
Let us know how it goes
If small number then one way to implement it is using a transformer/loop. It will be a matter of aggregating the values into a pipe separated list of ValX,ValY,ValCol | ValX,ValY,ValCol| ... This pipe list that will be your looping values
In a transformer, for each Val1, Val2 pair will be evaluated against the looping list created above to get get the ValCol and exit out the loop once you get a match...process the next pair. In the loop you would need to parse out the ValX, ValY and ValCol to use it as below
If ValX < Val2 < ValY then ValCol
Let us know how it goes
Julio Rodriguez
ETL Developer by choice
"Sure we have lots of reasons for being rude - But no excuses
ETL Developer by choice
"Sure we have lots of reasons for being rude - But no excuses