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