Overlapping intervals

[MindaugasBacius]

Hello,

I am still face up a problem while removing overlapping intervals.

The data:

Client	Product	From	To
A	A	2015.01.01	2015.01.15
A	A	2015.01.15	2015.01.15
A	A	2015.01.10	2015.01.20
A	A	2015.02.01	2015.02.10
B	B	2015.01.01	2015.01.10
B	B	2015.01.15	2015.01.20
B	B	2015.01.13	2015.01.13
B	B	2015.01.13	2015.01.15
B	C	2015.01.01	2015.01.30
B	C	2015.01.10	2015.02.15
B	C	2015.02.20	2015.02.25
B	C	2015.02.19	2015.02.21
B	C	2015.02.23	2015.02.26

The result should be:

Client	Product	From	To
A	A	2015.01.01	2015.01.20
A	A	2015.02.01	2015.02.10
B	B	2015.01.01	2015.01.10
B	B	2015.01.13	2015.01.20
B	C	2015.01.01	2015.02.15
B	C	2015.02.19	2015.02.26

I need to remove overlapping intervals depending on Client and Product fields.

Thank you for your help!

[raajaswin]

Have you tried this?

[MindaugasBacius]

Unfortunately this do not work :

[raajaswin]

Check this, still i have doubt on this.

[MindaugasBacius]

Not working.

[cwolf]

Hi Mindaugas,

try following:

// 1. Dissolving intervals into discrete rows


TEMP:
LOAD 
Min(From) as MinFrom,
Max(To) as MaxTo 
Resident TEST; 
let vMinFrom=FieldValue('MinFrom',1);
let vMaxTo=FieldValue('MaxTo',1);


DROP Table TEMP;


DATES:
LOAD
RecNo()+$(vMinFrom)-1 as Date
AutoGenerate $(vMaxTo)-$(vMinFrom)+1;


TEMP:
IntervalMatch(Date)
LOAD
From,
To
Resident TEST;


Left Join (TEST)
LOAD Distinct * Resident TEMP;


DROP Fields From,To;
DROP Tables TEMP,DATES;


// 2. Creating new intervals


TEMP:
LOAD
*,
if(Client=Previous(Client) and Product=Previous(Product) and Date-Previous(Date)<2,Peek('No'),if(IsNull(Peek('No')),1,Peek('No')+1)) as No
Resident TEST
Order By Client,Product,Date;


DROP Table TEST;


Left Join(TEMP)
LOAD
No,
Date(Min(Date)) as From,
Date(Max(Date)) as To
Resident TEMP
Group by No;


DROP Field Date;


TEST:
NoConcatenate
LOAD Distinct * Resident TEMP;


DROP Table TEMP;

[MindaugasBacius]

It works great on small scale.

But if I have period of 5 years and 1.000.000 distinct intervals the method struggles to do it's job.

Maybe you have any other ideas?

[cwolf]

The script can be optimized a little bit. See attachment.

[MindaugasBacius]

Actually the problem appears in this part of the script as there are creating millions of rows.

TEMP:
IntervalMatch(Date)
LOAD
From,
To
Resident TEST;


Left Join (TEST)
LOAD Distinct * Resident TEMP;

[sunny_talwar]

Another (although similar) option

Table:

LOAD DISTINCT Client,

  Product,

  Date(From + IterNo() - 1) as Date

While From + IterNo() - 1 <= To;

LOAD * INLINE [

    Client, Product, From, To

    A, A, 2015.01.01, 2015.01.15

    A, A, 2015.01.15, 2015.01.15

    A, A, 2015.01.10, 2015.01.20

    A, A, 2015.02.01, 2015.02.10

    B, B, 2015.01.01, 2015.01.10

    B, B, 2015.01.15, 2015.01.20

    B, B, 2015.01.13, 2015.01.13

    B, B, 2015.01.13, 2015.01.15

    B, C, 2015.01.01, 2015.01.30

    B, C, 2015.01.10, 2015.02.15

    B, C, 2015.02.20, 2015.02.25

    B, C, 2015.02.19, 2015.02.21

    B, C, 2015.02.23, 2015.02.26

];

TempTable:

LOAD *,

  If(Client = Previous(Client) and Product = Previous(Product) and Date = Previous(Date + 1), Alt(Peek('SNo'), 1), RangeSum(Peek('SNo'), 1)) as SNo

Resident Table

Order By Client, Product, Date;

FinalTable:

LOAD Client,

  Product,

  SNo,

  Date(Min(Date)) as From,

  Date(Max(Date)) as To

Resident TempTable

Group By Client, Product, SNo;

DROP Table Table, TempTable;