Qlik Community

Qlik Sense Advanced Authoring

Discussion board where members can learn more about Qlik Sense Advanced Authoring.

Announcements
See why Qlik is recognized as a Leader for the 10th year in a row – and discover how Qlik can help put your business in the lead. Get Report
Highlighted
Partner
Partner

Min and Max Dates

I have a table loaded like this:

Load
Item,
Start Date,
End Date,
Rate
From Table;

For one single item it looks like this:

originaldates.png

 

 

 

 

 

 

 

 

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/20156/30/2015$0.20
7/1/20159/30/2015$0.21
10/1/20153/31/2016$0.20
4/1/20166/30/2016$0.21
7/1/201612/31/2016$0.20
1/1/20173/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.
baddates.png

 

 

 

 

 

Any ideas on what to try?

1 Solution

Accepted Solutions
Highlighted

Re: Min and Max Dates

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];

View solution in original post

3 Replies
Highlighted
Partner
Partner

Re: Min and Max Dates

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)

Highlighted

Re: Min and Max Dates

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];

View solution in original post

Highlighted
Partner
Partner

Re: Min and Max Dates

Thanks, Sunny. That will work!