DSXchange

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?

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.

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