topic Find number of minutes used in an hour in QlikView

Find number of minutes used in an hour

andrewmo — Wed, 01 Mar 2017 18:54:56 GMT

I need to evaluate the total number of room minutes used for each hour in a day.

For example between 0:00 AM and 1:00 AM there were 3 cases.

Case 1 enters at 0:20 and leaves at 3:20

Case 2 enters at 0:55 and leaves at 2:15

Case 3 enters at 0:35 and leaves at 1:30

Room minutes for 0:00-1:00 would be 70 minutes (1:00 - 0:20 = 40 MINS, 1:00 - 0:55 = 5 MINS, 1:00 - 0:35 = 25 MINS)

I would then need to iterate through each hour of the day.

I tried interval match which was able to get me the count of cases for each hour, but I could not figure out how to calculate the sum of minutes used in said hour.

Thank you

Re: Find number of minutes used in an hour

MarcoWedel — Wed, 01 Mar 2017 23:31:04 GMT

Hi,

maybe one solution might be:

tabSampleData:
LOAD *,
    Time#(Time([Case Enters]+(1-[Case Enters])*Rand(),'hh:mm'),'hh:mm') as [Case Leaves];
LOAD RecNo() as Case,
    Ceil(Rand()*10) as Room,
    Time#(Time(RangeMin(RangeMax(NORMINV(Rand(),0.4,0.2),0),0.95),'hh:mm'),'hh:mm') as [Case Enters]
AutoGenerate 30;
   
tabTimeLink:
LOAD Case,
    Time#(Time([Case Enters]+(IterNo()-1)/1440,'hh:mm'),'hh:mm') as Time
Resident tabSampleData
While Time#(Time([Case Enters]+(IterNo()-1)/1440,'hh:mm'),'hh:mm') <= [Case Leaves];   
   
tabTime:
LOAD *,
    Hour(Time) as Hour,
    Minute(Time) as Minute;
LOAD Time(Time#(RecNo()-1,'m'),'hh:mm') as Time
AutoGenerate 1440;

hope this helps

regards

Marco

Re: Find number of minutes used in an hour

andrewmo — Thu, 02 Mar 2017 15:18:39 GMT

this looks like it should do the trick....thanks much!!!

Re: Find number of minutes used in an hour

MarcoWedel — Sun, 05 Mar 2017 21:17:23 GMT

You're welcome.

I'm glad it worked for you.

regards

Marco