Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table loaded like this:
Load
Item,
Start Date,
End Date,
Rate
From Table;
For one single item it looks like this:
I want to keep only the min and max start/end dates IF THE DATES ARE SEQUENTIAL.
I want the results to look like this:
1/1/2015 | 6/30/2015 | $0.20 |
7/1/2015 | 9/30/2015 | $0.21 |
10/1/2015 | 3/31/2016 | $0.20 |
4/1/2016 | 6/30/2016 | $0.21 |
7/1/2016 | 12/31/2016 | $0.20 |
1/1/2017 | 3/31/2017 | $0.24 |
However, if I try something like this:
Load
Item,
Min(Start Date) as Start Date,
Max(End Date) as End Date,
Rate
From Table
Group by Item, Rate;
I get the min and max of ALL the dates, not only the dates that are in sequential order. I do not want the Max End Date of a line to overlap with the next min date.
Any ideas on what to try?
Here is one way you can do this
Table:
LOAD * INLINE [
Start Date, End Date, Rate
01/01/2015, 03/31/2015, 0.20
04/01/2015, 06/30/2015, 0.20
07/01/2015, 09/30/2015, 0.21
10/01/2015, 12/31/2015, 0.20
01/01/2016, 03/31/2016, 0.20
04/01/2016, 06/30/2016, 0.21
07/01/2016, 09/30/2016, 0.20
10/01/2016, 12/31/2016, 0.20
01/01/2017, 03/31/2017, 0.24
];
FinalTable:
LOAD *,
If(Rate = Previous(Rate), Peek('Start Date New'), [Start Date]) as [Start Date New]
Resident Table
Order By [Start Date];
Right Join (FinalTable)
LOAD [Start Date New],
Rate,
Max([End Date]) as [End Date]
Resident FinalTable
Group By [Start Date New], Rate;
DROP Table Table;
DROP Field [Start Date];
RENAME Field [Start Date New] to [Start Date];
Hi,
try to create a new field like this:
RowNo () AS NR
then order by that and create a new field, so you could use it in group by:
If(rate=Previous(rate),Peek('NR'),NR)
Here is one way you can do this
Table:
LOAD * INLINE [
Start Date, End Date, Rate
01/01/2015, 03/31/2015, 0.20
04/01/2015, 06/30/2015, 0.20
07/01/2015, 09/30/2015, 0.21
10/01/2015, 12/31/2015, 0.20
01/01/2016, 03/31/2016, 0.20
04/01/2016, 06/30/2016, 0.21
07/01/2016, 09/30/2016, 0.20
10/01/2016, 12/31/2016, 0.20
01/01/2017, 03/31/2017, 0.24
];
FinalTable:
LOAD *,
If(Rate = Previous(Rate), Peek('Start Date New'), [Start Date]) as [Start Date New]
Resident Table
Order By [Start Date];
Right Join (FinalTable)
LOAD [Start Date New],
Rate,
Max([End Date]) as [End Date]
Resident FinalTable
Group By [Start Date New], Rate;
DROP Table Table;
DROP Field [Start Date];
RENAME Field [Start Date New] to [Start Date];
Thanks, Sunny. That will work!