topic Re: Show minutes used in Hour in QlikView

Show minutes used in Hour

jeckstein — Tue, 07 Nov 2017 17:13:04 GMT

Hi all,

I have a table that looks like this

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

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

ID	Date	Hour	Minutes Used in Hour
A	11/8/2017	8	30
A	11/8/2017	9	30
B	11/8/2017	10	30
B	11/8/2017	11	60
C	11/9/2017	6	30
C	11/9/2017	7	60
C	11/9/2017	8	60
D	11/9/2017	12	30

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

Re: Show minutes used in Hour

sunny_talwar — Tue, 07 Nov 2017 17:29:34 GMT

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

];

Re: Show minutes used in Hour

Anonymous — Tue, 07 Nov 2017 17:30:53 GMT

is like this?

Re: Show minutes used in Hour

jeckstein — Tue, 07 Nov 2017 17:44:49 GMT

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.

Re: Show minutes used in Hour

jeckstein — Tue, 07 Nov 2017 17:45:34 GMT

Did you mean to attach a link?

Re: Show minutes used in Hour

sunny_talwar — Tue, 07 Nov 2017 17:56:29 GMT

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

];

Re: Show minutes used in Hour

jeckstein — Tue, 07 Nov 2017 22:14:37 GMT

Sunny,

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

Re: Show minutes used in Hour

swuehl — Tue, 07 Nov 2017 22:25:33 GMT

Maybe like this

Utilization per hour

Re: Show minutes used in Hour

jeckstein — Wed, 08 Nov 2017 13:00:56 GMT

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

Re: Show minutes used in Hour

swuehl — Wed, 08 Nov 2017 13:24:06 GMT

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;

Re: Show minutes used in Hour

jeckstein — Wed, 08 Nov 2017 15:56:12 GMT

The time function for the field Bucket_Time is causing me issues. there are duplicate values here. I believe because this function is still somehow associated with date

Re: Show minutes used in Hour

swuehl — Wed, 08 Nov 2017 16:12:46 GMT

Use

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

Re: Show minutes used in Hour

swuehl — Wed, 08 Nov 2017 16:18:33 GMT

The complete script:

Set TimestampFormat = 'M/D/YY h:mmtt';

INPUT:

LOAD Recno() as RecID, *, Round((End_time -Start_time)*(60*24)) as Duration INLINE [

Start_time, End_time

1/1/17 9:45am, 1/1/17 10:00am

1/1/17 10:15am, 1/1/17 12:30pm

1/1/17 1:45pm, 1/1/17 2:45pm

1/1/17 3:15pm, 1/1/17 5:00pm

1/1/17 5:41pm, 1/1/17 6:23pm

1/1/17 7:15pm, 1/1/17 10:30pm

1/1/17 9:23pm, 1/3/17 10:10am

];

LOAD RecID,

Iterno() as BucketID,

Time#(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 Timestamp#(Timestamp(Floor(Start_time+(iterno()-1)*MakeTime(0,30),MakeTime(0,30)))) < End_time;

Re: Show minutes used in Hour

jeckstein — Thu, 07 Dec 2017 02:47:40 GMT

How would I change this code to do the same calculation for every minute rather then half hour?

Thanks in advance!

Re: Show minutes used in Hour

jeckstein — Thu, 07 Dec 2017 03:55:09 GMT

What about taking this down to the minute lvl?

Re: Show minutes used in Hour

swuehl — Thu, 07 Dec 2017 09:09:44 GMT

The basic idea is always the same, create a loop and create your buckets:

Set TimestampFormat = 'M/D/YY h:mmtt';

INPUT:

LOAD Recno() as RecID, *, Round((End_time -Start_time)*(60*24)) as Duration INLINE [

Start_time, End_time

1/1/17 9:45am, 1/1/17 10:00am

1/1/17 10:15am, 1/1/17 12:30pm

1/1/17 1:45pm, 1/1/17 2:45pm

1/1/17 3:15pm, 1/1/17 5:00pm

1/1/17 5:41pm, 1/1/17 6:23pm

1/1/17 7:15pm, 1/1/17 10:30pm

1/1/17 9:23pm, 1/3/17 10:10am

];

LOAD RecID,

Iterno() as BucketID,

Time#(Time(Round(Start_time+(iterno()-1)*MakeTime(0,1),MakeTime(0,1)))) as Bucket_Time,

1 as DurationBucket

Resident INPUT

WHILE iterno()-1 < Round((End_time-Start_time)*24*60);