Date period problem...

piekepotloed — Tue, 28 Apr 2020 14:24:37 GMT

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

Re: Date period problem...

rubenmarin — Thu, 30 Apr 2020 07:33:03 GMT

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;

topic Re: Date period problem... in QlikView

Date period problem...

Re: Date period problem...