Minimize data loaded

[Antoine]

Hello,

I have a table like this one:

Product	start Date	End Date
A	01/01/2018	06/01/2018
A	06/01/2018	08/01/2018
A	10/01/2018	14/01/2018
B	03/01/2018	05/01/2018
B	05/01/2018	06/01/2018
A	14/01/2018	20/01/2018

And to minimize the amount of lines, I want to be able for subsequent range of start Date / End date to turn the table into the following one:

Product	start Date	End Date
A	01/01/2018	08/01/2018
A	10/01/2018	20/01/2018
B	03/01/2018	06/01/2018

Is it possible through a script ?

[sunny_talwar]

You are clubbing the two rows for a product into a single row? So, if you began with 1000 rows, the final result will be 500 rows?

[Antoine]

Yes, but only if the "end Date" = "start Date" for the same product. So the exact amount of lines would depend on how often I have this case...

Product	start Date	End Date
A	01/01/2018	06/01/2018
A	06/01/2018	08/01/2018

meaning 

A = [01/01/2018 ; 06/01/2018] U [06/01/2018 ; 08/01/2018]

A = [01/01/2018 ; 08/01/2018]

I could even have a 3rd line with A 08/01/2018 to 09/01/2018, and I would still need to turn it into one single range...

[sunny_talwar]

Make sense... can you try this

Table:
LOAD * INLINE [
    Product, start Date, End Date
    A, 01/01/2018, 06/01/2018
    A, 06/01/2018, 08/01/2018
    A, 10/01/2018, 14/01/2018
    B, 03/01/2018, 05/01/2018
    B, 05/01/2018, 06/01/2018
    A, 14/01/2018, 20/01/2018
];

TempTable:
LOAD Product,
	 If(Product = Previous(Product),
	 	If([start Date] = Previous([End Date]), Peek('New_Start_Date'), [start Date]), [start Date]) as New_Start_Date,
	 [start Date], 
	 [End Date]
Resident Table
Order By Product, [start Date];

Right Join (TempTable)
LOAD Product,
	 New_Start_Date,
	 Max([End Date]) as [End Date]
Resident TempTable
Group By Product, New_Start_Date;

FinalTable:
NoConcatenate
LOAD Product,
	 New_Start_Date as [start Date],
	 [End Date]
Resident TempTable;

DROP Tables Table, TempTable;

[OmarBenSalem · Accepted solution]

Can u please try:

t0:
load * Inline [
Product, startDate, EndDate
A ,01/01/2018,06/01/2018
A ,06/01/2018,08/01/2018
A ,10/01/2018,14/01/2018
B ,03/01/2018,05/01/2018
B ,05/01/2018,06/01/2018
A ,14/01/2018,20/01/2018
];

NoConcatenate
t:
load * ,RowNo() as row ,Peek(EndDate) , if( startDate=Peek(EndDate),1,0) as flag Resident t0 Order by Product;
drop table t0;

final:
NoConcatenate
load Product, date(min(startDate)) as StartDate, date(max(EndDate)) as EndDate Resident t where flag=1 and peek(flag)=0 Group by Product ;
Concatenate (final)
Load
Product,startDate as StartDate,EndDate Resident t Where flag=0 ;
Drop Table t;

 result: