topic Hourly Census graph in QlikView

Hourly Census graph

Wed, 08 Dec 2010 21:37:41 GMT

I am trying to create a graph from data to show an x-axis that contains Care Units (for Emergency room patients in beds at the time) and the y-axis shows each hour of the day (in military time). So the y-axis will have 00 for midnight hour, 01 for 1am hour, 02 for 2am hour and so on through 23 for 11 pm hour.

The goal is to show a count of patients in the Emergency care rooms for each hour of the day (this will be used for staffing purposes). So, if a patient arrives at midnight and is in Care Unit 2 for 3 1/2 hours, then they will be counted for the 00 hour, 01 hour, 02 hour, and 03 hour on the graph for care unit 2. I have their arrive date/time and discharge date/time as well as their LOS (Length of stay). The length of stay is in hh:mm format. I am having trouble trying to figure out a way to graph this.

Any thoughts would be greatly appreciated since I am fairly new to QlikView.

Regards,

nmellick

Hourly Census graph

rwunderlich — Tue, 21 Dec 2010 00:39:37 GMT

One approach would be to generate a Calendar table of each hour and date. Then use IntervalMatch to connect the patient visits into the Calendar hours. See attached example.

-Rob

Hourly Census graph

Wed, 22 Dec 2010 22:22:31 GMT

Thanks so much Rob! This solution appears to work the way I need.

Re: Hourly Census graph

Sat, 08 Feb 2014 18:19:02 GMT

I have Personal Edition so I'm unable to open your file. Could you upload a PE version or provide some details of your solution?

Many thanks

Jim

Re: Re: Hourly Census graph

rwunderlich — Sun, 09 Feb 2014 00:32:14 GMT

I can't provide a PE copy, but here's the script.

data:

LOAD *,

// Round down to get starting hour for IntervalMatch

timestamp(floor(Arrive) + Maketime(hour(Arrive)), 'MM/DD/YYYY hh') as ArriveHour

;

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

]

;

// Get date range

date_temp:

LOAD

min(floor(Arrive)) as mindate,

max(floor(Discharge)) as maxdate

RESIDENT data

;

LET vMindate = peek('mindate')-1;

LET vMaxdate = peek('maxdate');

DROP TABLE date_temp;

// Generate a calendar table with one row for each hour each date

FOR i = 0 to 23 // Hour 0 to 23

Calendar:

LOAD *,

timestamp(Date + Maketime($(i)), 'MM/DD/YYYY hh') as DateHour,

$(i) as Hour

;

LOAD

date($(vMindate) + IterNo()) as Date

AUTOGENERATE 1

WHILE $(vMindate) + IterNo() <= $(vMaxdate)

;

NEXT i

// IntervalMatch to connect Patient visits to the Calendar table

IntervalMatch (DateHour)

LOAD ArriveHour, Discharge

RESIDENT data

;

Re: Hourly Census graph

Sun, 09 Feb 2014 15:17:45 GMT

Thanks for responding so quickly Rob. I'll try this in my application.

Thanks again

Jim

Re: Hourly Census graph

Sun, 16 Feb 2014 10:59:59 GMT

Hell Rob

Thanks again for your help with this however I'm struggling with how my load from a local Excel file fits in with your script. I've replaced your Inline section with my load, ie:

data:

LOAD *,

// Round down to get starting hour for IntervalMatch

timestamp (floor(Arrive) + Maketime(hour(Arrive)),

'DD/MM/YYYY hh') as ArriveHour

;

LOAD

Patient,

Unit,

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

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

FROM

[Data_1.xlsx]

coxml, embedded labels, table is Sheet1);

// Get date range

date_Temp:

....and so on but the vMindate and vMaxdate variables aren't picking anything up which is where I think the problem may be.

Any ideas?

Thanks again

Jim

Re: Hourly Census graph

rwunderlich — Sun, 16 Feb 2014 16:54:47 GMT

Hard to say without seeing your entire script. Can you post all of it?

-Rob

Re: Re: Hourly Census graph

Sun, 16 Feb 2014 17:31:15 GMT

I'm unable to paste directly into this reply box for some reason so I've attached a Word doc with the script. Hope this is okay.

Jim

Re: Re: Hourly Census graph

Anonymous — Tue, 04 Oct 2016 14:56:58 GMT

Hi Rob,

Thanks for post the hourly census qvw, that's very helpful.

I have one question: it works perfectly, only when i select a certain month, the first day of the month wouldn't have the count right, because the census count also need to count in patients admitted from previous date so it's a rolling total count. How can i fix that?

thank you!

LeeAnn

Re: Re: Hourly Census graph

Anonymous — Thu, 06 Oct 2016 00:45:47 GMT

Hi Rob,

Thanks for post the hourly census qvw in Qlik community, that's very helpful.

thank you!

LeeAnn

Re: Hourly Census graph

rwunderlich — Thu, 06 Oct 2016 20:06:34 GMT

LeeAnn,

It sounds like you have two dates: AdmitDate and DisChargeDate. Can you post a sample?

-Rob

Re: Hourly Census graph

Anonymous — Fri, 07 Oct 2016 15:22:16 GMT

thanks for replying Rob!

Oh... I know why my first selected day is not correct, I have an existing calendar in my data model so when I replicate the hourly census I created a new Calendar.

So in the following look should I replace ' date($(vMindate) + IterNo()) as Date' to the date name in my existing data model?

FOR i = 0 to 23 // Hour 0 to 23
Calendar:
LOAD *,
timestamp(Date + Maketime($(i)), 'MM/DD/YYYY hh') as DateHour,
$(i) as Hour
;
LOAD
date($(vMindate) + IterNo()) as Date
AUTOGENERATE 1
WHILE $(vMindate) + IterNo() <= $(vMaxdate)
;
NEXT i

Re: Hourly Census graph

Anonymous — Sat, 08 Oct 2016 12:02:03 GMT

Hi Rob

I have attached an example. Basically I already have an existing calendar in my app and it's linked to my 'Data' table by this key: %DateKey. If I were to implement my census using the existing calenda I'm gonna have to break the previous link by %DateKey and only let interval match to link the calendar and Data table. But I do need the previous linkage (%DateKey) for other things...

What should I do?

(in the attach, I commented out %DateKey field in Calendar and census works fine, but how should I modify it so I don't have to lose my previous linkage for it to work?)

thanks a lot for your help!!

LeeAnn