Date period problem...

[piekepotloed]

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

[rubenmarin · Accepted solution]

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;