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

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

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

Community Browser