Interval Match multiple keys

andrewmo — Tue, 07 Mar 2017 19:37:56 GMT

Hello

I am trying to script an interval match function which would show me a number of minutes that occur between a start and stop time.

Here is some example data:

CASES:

LOAD * INLINE

[

Log,Serv,Date,In,Out, RoomMins, RoomID

123,Ortho,2/1/2017, 7:20,12:45,325, 4

345,Ortho,2/1/2017, 9:15,11:20,125, 3

567,Ortho,2/1/2017, 12:15,16:18,243,3

789,Uro,2/1/2017, 11:15,13:46,151,5

];

BLOCK:

LOAD * INLINE

[

Serv,Date,Start,Stop, NumRooms

Ortho,2/1/2017,7:20,15:00,1

Uro,2/2/2017,7:20,17:00,1

];

I need to be able to show the number of minutes that occurred between the Start and Stop times for each service. Using the above data I would expect the following:

On 2/1 Ortho has one Room available

On 2/1 Ortho had 3 logs:

Since Log 123 was the first to start and it is an Ortho case, all of the RoomMins would be considered In Block

Log 345's RoomMins would be considered Out of Block since Ortho only had 1 block available which was being used by Log 123

Log 567 would have 135 of its 243 minutes considered In Block as Log 123 finished shortly after Log 567 started and Ortho still has available time until 15:00 (15:00-12:45 = 135 minutes)

The Uro case would be considered Out of Block as they did not have block on this day.

Thanks in advance.

topic Interval Match multiple keys in QlikView

Interval Match multiple keys