Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jeckstein
Partner - Creator
Partner - Creator

Show minutes used in Hour

Hi all,

I have a table that looks like this

IDDateStart TimeEnd Time
A11/8/20179:30:0010:30:00
B11/8/201711:30:0013:00:00
C11/9/20177:30:0010:00:00
D11/9/201713:30:0014:00:00

I am looking to preform some back end scripting to get the following result

IDDateHourMinutes Used in Hour

A

11/8/2017830
A11/8/2017930
B11/8/20171030
B11/8/20171160
C11/9/2017630
C11/9/2017760
C11/9/2017860
D11/9/20171230

What I am looking for is to show how many minutes were used in each hour. For example

ID C starts at 7:30 am and ends at 10:00 am.

30 minutes is used in the 6th hour, 60 minutes used in the 9th hour, and 60 mintues used in the 10th hour.

(The hour count starts at 0 so midnight-1 am is considered hour 0, 1am-2am is considered hour 1 etc.)

Please let me know if you have any ideas!!

Thanks in advanced!!

1 Solution

Accepted Solutions
15 Replies
sunny_talwar

May be this

Table:

LOAD *,

Hour([Start Time] + MakeTime(IterNo() - 1))-1 as Hour,

Interval(If([Start Time] >= [Start Time] + MakeTime(IterNo() - 1), Ceil([Start Time], 1/24) - [Start Time],

If([End Time] <= [Start Time] + MakeTime(IterNo() - 1), [End Time] - Floor([End Time], 1/24), 1/24)), 'mm') as [Minutes Used in Hour]

While [Start Time] + MakeTime(IterNo() - 1) <= [End Time];

LOAD * INLINE [

    ID, Date, Start Time, End Time

    A, 11/8/2017, 9:30:00, 10:30:00

    B, 11/8/2017, 11:30:00, 13:00:00

    C, 11/9/2017, 7:30:00, 10:00:00

    D, 11/9/2017, 13:30:00, 14:00:00

];

Anonymous
Not applicable

is like this?

jeckstein
Partner - Creator
Partner - Creator
Author

Sunny,

This is close. when applied to my real data set it seems that some hours are getting left out. For example I have a ID that has the following

ID,Date,Start,End

A,2017-06-03,7:30:00,9:08:00

The result this code is giving me is the following

ID,Date,Hour,Start,End

A,2017-06-03,6,30

A,2017-06-03,7,60


However there is no row for the 8th hour with 8 mins.

jeckstein
Partner - Creator
Partner - Creator
Author

Did you mean to attach a link?

sunny_talwar

Try this

Table:

LOAD *

Where [Minutes Used in Hour] <> 0;

LOAD *,

Hour([Start Time] + MakeTime(IterNo() - 1))-1 as Hour,

Interval(If([Start Time] >= [Start Time] + MakeTime(IterNo() - 1), Ceil([Start Time], 1/24) - [Start Time],

If([End Time] <= [Start Time] + MakeTime(IterNo() - 1), [End Time] - Floor([End Time], 1/24), 1/24)), 'mm') as [Minutes Used in Hour]

While Hour([Start Time] + MakeTime(IterNo() - 1)) <= Hour([End Time]);

LOAD * INLINE [

    ID, Date, Start Time, End Time

    A, 11/8/2017, 9:30:00, 10:30:00

    B, 11/8/2017, 11:30:00, 13:00:00

    C, 11/9/2017, 7:30:00, 10:00:00

    D, 11/9/2017, 13:30:00, 14:00:00

    E, 3/6/2017,  7:30:00,  9:08:00

];

jeckstein
Partner - Creator
Partner - Creator
Author

Sunny,

This is giving me the same issue. Some hours usually the last hour is getting dropped off

jeckstein
Partner - Creator
Partner - Creator
Author

Is there a way to do this by half hour intervals instead of full hour?

swuehl
MVP
MVP

Yes, should be possible, chnage the code to something like

LOAD RecID,

Iterno() as BucketID,

Time(Floor(Start_time+(iterno()-1)*MakeTime(0,30),MakeTime(0,30))) as Bucket_Time,

Round(

(RangeMin(End_time,Floor(Start_time+(iterno())*MakeTime(0,30), MakeTime(0,30)))

-RangeMax(Start_time,Floor(Start_time+(iterno()-1)*MakeTime(0,30), MakeTime(0,30))))

*60*24) as DurationBucket

Resident INPUT

WHILE Floor(Start_time+(iterno()-1)*MakeTime(0,30),MakeTime(0,30)) < End_time;