Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Thanks so much Rob! This solution appears to work the way I need.
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
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
;
Thanks for responding so quickly Rob. I'll try this in my application.
Thanks again
Jim
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
Hard to say without seeing your entire script. Can you post all of it?
-Rob
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
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