Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Partner
Partner

Create dates between 2 dates

Hi,

I have a table in excel that I need to bring into Sense.  What I need is the table to show all dates between the admissiondate and the discharge date so from - 

PatientIDEventNumberAdmissionDateDischargeDateSequenceID
156858113/09/2017 10:0025/10/2017 13:304
169650110/08/2017 12:0011/10/2017 14:006
169770118/08/2017 12:0017/10/2017 14:347
169329105/09/2017 12:0031/10/2017 09:548
3000177127/09/2017 12:0012/01/2018 12:009

 

to

PatientIDEventNumberAdmissionDateDischargeDateSequenceIDDate
156858113/09/2017 10:0025/10/2017 13:30413/09/17
156858113/09/2017 10:0025/10/2017 13:30414/09/17
156858113/09/2017 10:0025/10/2017 13:30415/09/17
156858113/09/2017 10:0025/10/2017 13:30416/09/17
156858113/09/2017 10:0025/10/2017 13:30417/09/17

 

etc

Kev

1 Solution

Accepted Solutions

Re: Create dates between 2 dates

Try
Load
PatientID,
EventNumber,
AdmissionDate,
DischargeDate,
SequenceID,
Date(AdmissionDate + IterNo() -1) as Date
from tableName
while AdmissionDate + IterNo() - 1 < = DischargeDate;

Regards,
Great dreamer's dreams never fulfilled, they are always transcended.
5 Replies

Re: Create dates between 2 dates

Try
Load
PatientID,
EventNumber,
AdmissionDate,
DischargeDate,
SequenceID,
Date(AdmissionDate + IterNo() -1) as Date
from tableName
while AdmissionDate + IterNo() - 1 < = DischargeDate;

Regards,
Great dreamer's dreams never fulfilled, they are always transcended.
Partner
Partner

Re: Create dates between 2 dates

This creates millions of rows

Re: Create dates between 2 dates

It is as per your requirement only.
Great dreamer's dreams never fulfilled, they are always transcended.
Partner
Partner

Re: Create dates between 2 dates

its because i had null, thank you

Partner
Partner

Re: Create dates between 2 dates

How can i set the discharge date to today and it still work? I've got the below but i think something with the "While" needs doing

vent:
LOAD
PatientID,
EventNumber,
AdmissionDate,
AdmissionDate as CalendarKey,
Date(AdmissionDate + IterNo() -1) as Date,
if(DischargeDate='NULL',Today(),DischargeDate) as DischargeDate,
SequenceID
FROM [..\..\.xlsx]
(ooxml, embedded labels, table is ImsEvent)
while AdmissionDate + IterNo() < DischargeDate;