Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Find number of dates in time interval on master calendar

Good day,

I have the following two tables, and have multiple dates to create a master calendar. 

Metrics:

the number of times a patient was admitted to a department.

the the of days between startevent and endevent time based on the master calendar.

since the fields are very similar, I create a link table with a canonical date

Canonical Date

with admission date

and dates created using iterno() function to find dates between start date and end date and used the date field

in master calendar off of this date.

I also concatenated the tables.

My question, when I use a set statement to get the difference between the date fields it is giving me a false count.

Table1:

LOAD

    Patient,

    Unit,

    timestamp#(Arrive, 'MM/DD/YYYY hh:mm') as Arrive,

    timestamp#(Discharge, 'MM/DD/YYYY hh:mm') as Discharge

INLINE [

Patient, Unit, Arrive, Discharge

P1, A, 01/01/2010 09:00, 01/01/2010 12:30

P2, A, 01/01/2010 09:30, 01/01/2010 10:30

P3, A, 01/01/2010 11:15, 01/01/2010 16:40

P4, A, 01/01/2010 18:30, 01/02/2010 02:10

P5, A, 01/01/2010 01:15, 01/01/2010 01:30

P6, B, 01/01/2010 10:00, 01/01/2010 11:30

P7, B, 01/01/2010 10:15, 01/01/2010 10:30

P8, B, 01/01/2010 10:30, 01/01/2010 15:15

P9, B, 01/01/2010 13:00, 01/01/2010 15:30

P10,B, 01/02/2010 01:15, 01/02/2010 05:30

]

;

Table2:

LOAD

    Patient,

    Unit,

    timestamp#(Arrive, 'MM/DD/YYYY hh:mm') as Arrive,

    timestamp#(Discharge, 'MM/DD/YYYY hh:mm') as Discharge

INLINE [

Patient, Unit, Arrive, Discharge, EventStart, EventEnd

P1, A, 01/01/2010 09:00, 01/04/2010 12:30, 01/02/2010 09:30, 01/03/2010 12:30

P2, A, 01/01/2010 09:30, 01/04/2010 10:30, 01/02/2010 09:10, 01/03/2010 12:30

P3, A, 01/01/2010 11:15, 01/04/2010 16:40, 01/01/2010 09:00, 01/01/2010 12:30

P4, A, 01/01/2010 18:30, 01/04/2010 02:10, 01/01/2010 18:30, 01/02/2010 02:10

P5, A, 01/01/2010 01:15, 01/04/2010 01:30, 01/01/2010 01:15, 01/04/2010 01:30

P6, B, 01/01/2010 10:00, 01/04/2010 11:30, 01/01/2010 10:00, 01/01/2010 11:30

P7, B, 01/01/2010 10:15, 01/04/2010 10:30, 01/01/2010 10:15, 01/01/2010 10:30

P8, B, 01/01/2010 10:30, 01/04/2010 15:15, 01/01/2010 10:30, 01/01/2010 15:15

P9, B, 01/01/2010 13:00, 01/04/2010 15:30, 01/01/2010 13:00, 01/01/2010 15:30

P10,B, 01/02/2010 01:15, 01/04/2010 05:30, 01/02/2010 01:15, 01/02/2010 05:30

]

;

I would like to credit

Canonical Date

Hourly Census graphHourly Census graph