Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Date period problem...

Hi to you all,

I've got the following dataset:

Studentnr      Startdate   Enddate

1                        3-4-2020      10-4-2020

1                        18-4-2020   20-2-2020

2                       13-4-2020    14-4-2020

2                       14-4-2020    15-4-2020

2                       15-4-2020   18-4-2020

The result shoult look like this

Studentnr      Startdate   Enddate           Duration in day's

1                        3-4-2020      10-4-2020            8

1                        18-4-2020   20-2-2020            3

2                       13-4-2020    18-4-2020            6

I thought to solve this with previous() of peek(), but I cann't get it working. Who has a clue for me?

Thanks in advance.

Peter

 

1 Solution

Accepted Solutions
Highlighted

Re: Date period problem...

Hi Paul Peter, this script can do the trick:

 

GL_TABLE:
LOAD Studentnr, Date(Date#(Startdate, 'DD-MM-YYYY')) as Startdate, Date(Date#(Enddate, 'DD-MM-YYYY')) as Enddate INLINE [
Studentnr,      Startdate,   Enddate
1,                        3-4-2020,      10-4-2020
1,                        18-4-2020,   20-4-2020
2,                       13-4-2020 ,   14-4-2020
2,                       14-4-2020,    15-4-2020
2,                       15-4-2020,   18-4-2020
];

ProcessData:
NoConcatenate
LOAD
	Studentnr,
	If(Peek(Studentnr)=Studentnr and Startdate<=Peek(Enddate), Peek(Startdate), Startdate) as Startdate,
	Enddate
Resident GL_TABLE
Order by Studentnr, Startdate;

EndData:
NoConcatenate
LOAD
	*,
	Enddate-Startdate+1 as Duration
;
LOAD
	Studentnr,
	Startdate,
	Max(Enddate) as Enddate
Resident ProcessData
Group by Studentnr, Startdate;

DROP Table GL_TABLE;
DROP Table ProcessData;

 

View solution in original post

1 Reply
Highlighted

Re: Date period problem...

Hi Paul Peter, this script can do the trick:

 

GL_TABLE:
LOAD Studentnr, Date(Date#(Startdate, 'DD-MM-YYYY')) as Startdate, Date(Date#(Enddate, 'DD-MM-YYYY')) as Enddate INLINE [
Studentnr,      Startdate,   Enddate
1,                        3-4-2020,      10-4-2020
1,                        18-4-2020,   20-4-2020
2,                       13-4-2020 ,   14-4-2020
2,                       14-4-2020,    15-4-2020
2,                       15-4-2020,   18-4-2020
];

ProcessData:
NoConcatenate
LOAD
	Studentnr,
	If(Peek(Studentnr)=Studentnr and Startdate<=Peek(Enddate), Peek(Startdate), Startdate) as Startdate,
	Enddate
Resident GL_TABLE
Order by Studentnr, Startdate;

EndData:
NoConcatenate
LOAD
	*,
	Enddate-Startdate+1 as Duration
;
LOAD
	Studentnr,
	Startdate,
	Max(Enddate) as Enddate
Resident ProcessData
Group by Studentnr, Startdate;

DROP Table GL_TABLE;
DROP Table ProcessData;

 

View solution in original post