Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Partner
Partner

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
MVP
MVP

Re: Show minutes used in Hour

15 Replies

Re: Show minutes used in Hour

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

];

thiago_pinho
New Contributor III

Re: Show minutes used in Hour

is like this?

Partner
Partner

Re: Show minutes used in Hour

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.

Partner
Partner

Re: Show minutes used in Hour

Did you mean to attach a link?

Re: Show minutes used in Hour

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

];

Partner
Partner

Re: Show minutes used in Hour

Sunny,

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

MVP
MVP

Re: Show minutes used in Hour

Partner
Partner

Re: Show minutes used in Hour

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

MVP
MVP

Re: Show minutes used in Hour

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;