topic Count days for visit in QlikView

Count days for visit

dhborchardt — Thu, 07 Apr 2011 20:01:54 GMT

I have a table with VisitID, AdmitDateTime, and DischargeDateTime. I need to count the number of patients in the hospital each day. Each record(VisitID) could have 1 or more days. For example:

A- VisitID = 1 The patient was admitted on 1/1/2011 and discharged on 1/1/2011 that would be a count of 1.

B- VisitID = 2 The patient was admitted on 1/1/2011 and discharged on 1/3/2011 that would be a count of 3.

I have been trying to get the load script to take each record and break it out into individual records for each day so the two examples above would look like this in a new table:

XID VisitID XDate
1 1 1/1/2011
2 2 1/1/2011
3 2 1/2/2011
4 2 1/3/2011

Below I have included what I have so far. I know there is a lot wrong with it but I hope maybe it might help explain what I need to do. Maybe I am way off and there is a better way.

Visits:
LOAD * INLINE [
AdmitDateTime, DischargeDateTime, VisitID
1/1/2011, 1/1/2011, 1
1/1/2011, 1/2/2011, 2
1/2/2011, 1/2/2011, 3
1/2/2011, 1/3/2011, 4
1/2/2011, 1/4/2011, 5
1/2/2011, 1/5/2011, 6
];

LOAD
Visits AS XVisits,
Do While CalendarDate >= AdmitDateTime AND <= DischargeDateTime
CalendarDate = Xxxxxxxxxxxxxxxxx AS XDate
XID = Xxxxxxxxxxxxxxxxxxxxxxxxxxxx

Loop

RESIDENT Visits
;

DROP TABLE Visits;

Count days for visit

stevedark — Thu, 07 Apr 2011 20:19:30 GMT

Hi there,

To get the table structure how you are describing I suggest you search for information on the IntervalMatch statement - I suspect you may find a posting on the community describing how to achieve exactly what you are after.

Regards,
Steve

Count days for visit

dhborchardt — Thu, 07 Apr 2011 22:31:52 GMT

Thanks Steve

I banged my head against the wall for a while but got it to work with the IntervalMatch. Even thought it gives me synthetic keys it comes up with the correct result. I am guessing I need a left join somewhere in there. Any Recommendations?

Visits:
LOAD * INLINE [
AdmitDateTime, DischargeDateTime, VisitID
1/1/2011, 1/1/2011, 1
1/1/2011, 1/2/2011, 2
1/2/2011, 1/2/2011, 3
1/2/2011, 1/3/2011, 4
1/2/2011, 1/4/2011, 5
1/2/2011, 1/5/2011, 6
];

Calendar:
Load * INLINE [
DateNum, CalendarDate
1, 1/1/2011
2, 1/2/2011
3, 1/3/2011
4, 1/4/2011
5, 1/5/2011
6, 1/6/2011
];

CountDays:
INTERVALMATCH (CalendarDate)
LOAD
AdmitDateTime,
DischargeDateTime

Resident Visits
;

Count days for visit

stevedark — Thu, 07 Apr 2011 22:58:14 GMT

Hi there,

You just need to go through a couple more steps in order to clean up the data structure. The following should give what you are after:

Visits: LOAD * INLINE [ AdmitDateTime, DischargeDateTime, VisitID 1/1/2011, 1/1/2011, 1 1/1/2011, 1/2/2011, 2 1/2/2011, 1/2/2011, 3 1/2/2011, 1/3/2011, 4 1/2/2011, 1/4/2011, 5 1/2/2011, 1/5/2011, 6];Calendar:Load * INLINE [ DateNum, CalendarDate 1, 1/1/2011 2, 1/2/2011 3, 1/3/2011 4, 1/4/2011 5, 1/5/2011 6, 1/6/2011];LEFT JOIN (Calendar)INTERVALMATCH (CalendarDate)LOAD AdmitDateTime, DischargeDateTimeResident Visits;LEFT JOIN (Calendar)LOAD AdmitDateTime, DischargeDateTime, VisitIDResident Visits;DROP FIELD AdmitDateTime FROM Calendar;DROP FIELD DischargeDateTime FROM Calendar;

Hope that helps.

Regards,
Steve

Count days for visit

dhborchardt — Fri, 08 Apr 2011 13:43:53 GMT

Steve

That did the trick.

Usually by the time I get a new function to work I understand what is going on with it. In the case of IntervalMatch, I am still lost. I know how to use it in this case but I don't understand what is going on. How does it magically know I want to count the days? Could this be used somehow to count the hours within a day. Lets say someone was admitted the day before and discharged at noon today. That would be 12 hours on this day. Right now I do this sort of thing with a very long IF statement.

Thanks again

Count days for visit

stevedark — Fri, 08 Apr 2011 21:46:22 GMT

Hi there,

You probably could use an interval match - but for every day you would obviously have 24 rows and would end up with a load script that took a while to run and lots of rows for QlikView to work on at display time.

What you could probably do is stamp on each daily record how many hours (or even minutes) of that day the patient was in for. The best way to achieve this may be a resident load from the table you already have, stamping 1 on whole days in (i.e. where the day is not the first or last day of stay) and a fraction where the day is the first, last or both. You can then just sum up that column to find cumulative days between dates.

Hope that helps,

Cheers,
Steve