Discussion board where members can learn more about Qlik Sense Advanced Authoring.
I have a table loaded like this:
LoadItem,Start Date,End Date,RateFrom 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:
However, if I try something like this:
LoadItem,Min(Start Date) as Start Date,Max(End Date) as End Date,RateFrom TableGroup 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
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
If(Rate = Previous(Rate), Peek('Start Date New'), [Start Date]) as [Start Date New]
Order By [Start Date];
Right Join (FinalTable)
LOAD [Start Date New],
Max([End Date]) as [End Date]
Group By [Start Date New], Rate;
DROP Table Table;
DROP Field [Start Date];
RENAME Field [Start Date New] to [Start Date];
View solution in original post
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:
Thanks, Sunny. That will work!