DSXchange

Hi,

I have data like this.
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!

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.

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.