Range Lookup question

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
dnat
Participant
Posts: 200
Joined: Thu Sep 06, 2007 2:06 am

Range Lookup question

Post by dnat »

Hi,

I have data like this.

Code: Select all

key, start, end
1,1,100
1,80,140
2,200,300
4,250,300
Here I am looking to reject/capture the first two records where there is an overlap. i.e for the same key, the end should not fall between the start and end of the other record(with the same key). and the start should not fall between the start and end of the other record. basically the overlapping records.

I tried using Range lookup, withe input and reference as the same but i am not able to apply both the conditions.

The condition should be start not between start and end OR end not between start and end.

Range lookup is only able to do AND condition.

Is there a way to do that?

Thanks!
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Use a Transformer stage, with sorted data, and stage variables to remember the previous record. Effect your test using output link constraint expression(s).

If you really need to capture the reject records, you probably need to use the looping functionality in the Transformer stage to set a flag that determines which output link the input link(s) should be transferred to.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
abhiramk
Participant
Posts: 2
Joined: Tue Dec 22, 2015 2:34 pm
Location: India

Post by abhiramk »

It can't be done using a range lookup. As Ray mentioned, it can be done using Sort and transformer, followed by stage variables to remember the previous value.

The overlapping can happen in 13 possible ways (Allen's interval algebra explains this). The 13 here is an exhaustive set. So, use stage variables to handle all the 13 scenarios. Pass the records that have an overlap to a new link and do a lookup with same data to reject the record that have a match.

All the best.
Cheers
Abhiram
Post Reply