Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
andrewmo
Contributor

Comparing minute of day with multiple variables

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

Tags (2)
3 Replies

Re: Compare start and stop times

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

thanks

regards

Marco

andrewmo
Contributor

Re: Compare start and stop times

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

Community Browser