Looking for design suggestions

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
abc123
Premium Member
Premium Member
Posts: 605
Joined: Fri Aug 25, 2006 8:24 am

Looking for design suggestions

Post by abc123 »

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?
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
JRodriguez
Premium Member
Premium Member
Posts: 425
Joined: Sat Nov 19, 2005 9:26 am
Location: New York City
Contact:

Post by JRodriguez »

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
Julio Rodriguez
ETL Developer by choice

"Sure we have lots of reasons for being rude - But no excuses
Post Reply