Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

andrewmo
Contributor

Find number of minutes used in an hour

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

Tags (1)
1 Solution

Accepted Solutions

Re: Find number of minutes used in an hour

Hi,

maybe one solution might be:

QlikCommunity_Thread_251806_Pic1.JPG

QlikCommunity_Thread_251806_Pic3.JPG

QlikCommunity_Thread_251806_Pic2.JPG

QlikCommunity_Thread_251806_Pic5.JPG

QlikCommunity_Thread_251806_Pic4.JPG

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

3 Replies

Re: Find number of minutes used in an hour

Hi,

maybe one solution might be:

QlikCommunity_Thread_251806_Pic1.JPG

QlikCommunity_Thread_251806_Pic3.JPG

QlikCommunity_Thread_251806_Pic2.JPG

QlikCommunity_Thread_251806_Pic5.JPG

QlikCommunity_Thread_251806_Pic4.JPG

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

Highlighted
andrewmo
Contributor

Re: Find number of minutes used in an hour

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

Re: Find number of minutes used in an hour

You're welcome.

I'm glad it worked for you.

regards

Marco