topic Re: Summing with intervalmatch in QlikView

Summing with intervalmatch

andrewmo — Wed, 15 Mar 2017 14:56:07 GMT

Hello

I am trying to build a display that shows the utilization of each surgical block in an operating room.

To do so I have to create three main tables:

A case details table that has the case times and locations

A master calendar table

A block calendar table that contains possible dates for each block based on the day of the week and week number of the month.

I then need to use a combination of all the above tables to be able to see the count of cases going at any minute of a given day versus the amount of rooms available for that specific block at that specific time.

I would like to see something similar to this:

The issue I have as seen in the pic above is I am not able to capture distinct minute of day (notice 8:21 and 8:28) nor am I able to figure out the logic to capture the number of cases going at that time of day.

I have attached a few dummy cases to my logic in the QVW below.

Please let me know if you have any suggestions.

Thanks again

Re: Summing with intervalmatch

adamdavi3s — Wed, 15 Mar 2017 14:59:15 GMT

Hi Andrew,

I'm pretty sure I've got a theatre utilisation dashboard I created before which I can share

I'll check my server when I have a moment (later this evening)

Re: Summing with intervalmatch

sunny_talwar — Wed, 15 Mar 2017 15:20:56 GMT

Try this

BLOCKMINS:

NoConcatenate

Load DISTINCT interval(Floor(frac(BlkMinOfDayRAW), 1/1440),'h:mm') as BlkMinOfDay,ServLine,Dt,BlockLoc,StartTime as BlkStart ,EndTime as BlkEnd Resident BLOCKTIME;

INNER JOIN (BLOCKMINS)

IntervalMatch(BlkMinOfDay,ServLine,Dt,BlockLoc)

LOAD StartTime as BlkStart, EndTime as BlkEnd

Resident BLOCKCAL;

Re: Summing with intervalmatch

andrewmo — Wed, 15 Mar 2017 15:26:45 GMT

Hi Sunny

Thanks for the advice. While this did solve the duplicate minute of day issue, now it appears the intervalmatch is off somehow:

8:27 should have a 2, but displays one. My expression for the Available column is =count(BlkStart) which comes from the BLOCKMINS load.

Re: Summing with intervalmatch

sunny_talwar — Wed, 15 Mar 2017 15:26:59 GMT

Or may be round

BLOCKMINS:

NoConcatenate

Load DISTINCT interval(Round(frac(BlkMinOfDayRAW), 1/1440),'h:mm') as BlkMinOfDay,ServLine,Dt,BlockLoc,StartTime as BlkStart ,EndTime as BlkEnd Resident BLOCKTIME;

INNER JOIN (BLOCKMINS)

IntervalMatch(BlkMinOfDay,ServLine,Dt,BlockLoc)

LOAD StartTime as BlkStart, EndTime as BlkEnd

Resident BLOCKCAL;

Re: Summing with intervalmatch

sunny_talwar — Wed, 15 Mar 2017 15:27:23 GMT

Try Round instead of Floor

Re: Summing with intervalmatch

andrewmo — Wed, 15 Mar 2017 15:32:45 GMT

Awesome! That looks like it should do the trick for the Available column, any advice for the Going column?

I thought the INMINS load statement would work but I can't figure it out:

INMINS:

NoConcatenate

LOAD DISTINCT interval(frac(MinOfDay),'h:mm') as BlkMinOfDayTEST,Dt,ServLine,Loc, count(distinct LOG_ID) as Cnt

RESIDENT CASESFIN

GROUP BY interval(frac(MinOfDay),'h:mm'),Dt,ServLine,Loc;

I was thinking my expression for Going should be =if(BlkMinOfDayTEST = BlkMinOfDay,Cnt,0) but it does not return any results

Re: Summing with intervalmatch

sunny_talwar — Wed, 15 Mar 2017 15:36:51 GMT

I am not sure what you are counting within Going? Would you be able to elaborate a little?

Re: Summing with intervalmatch

andrewmo — Wed, 15 Mar 2017 15:42:36 GMT

I want to be able to see the amount of LOG_IDs whose InTm and OutTm fall in the range of the BlkMinOfDay.

For example:

LOG_ID 123 has an InTm of 08:20 and an OutTm of 14:07

LOG_ID 345 has an InTm of 10:33 and an OutTm of 11:18

On the table with the BlkMinOfDay I would expect to see from 8:20-10:33 2 Available and 1 going, but then from 10:33-11:18 2 Available and 2 Going; then back to 2 Available and 1 going at 11:19..

Does that make sense?

Re: Summing with intervalmatch

sunny_talwar — Wed, 15 Mar 2017 17:41:43 GMT

Check the attached

Re: Summing with intervalmatch

andrewmo — Wed, 15 Mar 2017 18:16:58 GMT

Perfect, thanks again Sunny!

Re: Summing with intervalmatch

andrewmo — Wed, 15 Mar 2017 18:17:47 GMT

Hi Adam

Sunny gave me a great framework to work with, however I would still like to see what you have built.

Thanks again