Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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:
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...
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;
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: