topic Re: Compare start and stop times in QlikView

Comparing minute of day with multiple variables

andrewmo — Tue, 20 Dec 2016 15:47:22 GMT

Hello

I am trying to build load statements that would compare surgical block times to actual times to determine operating room utilization. I have tried using interval match but it doesn't seem to get me the results I expected.

I have one load statement that creates a table which contains the following elements:

- service line

- start date

- end date

- start time

- end time

- campus

- day of week

- week number

- room

In order for a case to be considered in block the case start and stop times must meet all the above criteria. For example:

Service Line Orthopedics has block on the 3rd Monday of the month at the Main OR between 0700-1500 in 2 different rooms.

Case A is an orthopedics case which occurred on the 3rd Monday of the month at the Main OR with a start time of 1200 and end time of 1600. This particular case would be considered in-block between 1200 and 1500 and then 1500-1600 would be considered out of block. I would also need to account for the number of rooms going at the same time to account for overbooking (if orthopedics had 3 cases going at 1200, 2 would be in-block and the third would be overbook)

What would be the best method for tackling this request?

Thanks in advance

Re: Compare start and stop times

MarcoWedel — Tue, 20 Dec 2016 22:01:35 GMT

please post an example application including some sample data as well as your expected result.

thanks

regards

Marco

Re: Compare start and stop times

andrewmo — Wed, 28 Dec 2016 16:17:26 GMT

I believe that is part of my problem; not sure how to script it for my expected result. Here is pseudo code:

CASES:

LOAD CASE_ID, PROC_DATE, PROC_DOW, PROC_WEEKNUM, IN_DTTM, OUT_DTTM, LOCATION, ROOM, PROV_ID, SERVLINE

FROM OR.QVD;

BLOCK:

LOAD SERVLINE, DOW, WEEKNUM, STARTDATE, ENDDATE, STARTTIME, ENDTIME, CAMPUS, ROOM

FROM BLK.QVD;

Here is the criteria for in-block:

SERVLINE from CASES = SERVLINE from BLOCK

PROC_DATE from CASES must be between STARTDATE and ENDDATE from Block AND

IN_DTTM from CASES must be >= STARTTIME from BLOCK AND

OUT_DTTM from CASES must be <= ENDTIME from BLOCK AND

PROC_DOW from CASES = DOW from BLOCK AND

PROC_WEEKNUM from CASES = WEEKNUM from BLOCK AND

LOCATION from CASES = CAMPUS from BLOCK AND

(tricky part)

How do I create a Boolean flag : if minute of day = minute of day from STARTDATE = 1 ELSE 0

I want to be able to see at 07:33 SERVLINE 'Ortho' had 3 cases going and at 07:35 SERVLINE 'Ortho' had 2 cases going because one finished...

Re: Compare start and stop times

MarcoWedel — Wed, 28 Dec 2016 22:09:56 GMT

maybe helpful:

QlikCommunity Tip: How to get answers to your post?

Its not about the answer, rather it’s about the question…

regards

Marco