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

15 Replies
jeckstein
Partner - Creator
Partner - Creator
Author

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

swuehl
MVP
MVP

Use

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

swuehl
MVP
MVP

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;

jeckstein
Partner - Creator
Partner - Creator
Author

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

Thanks in advance!

jeckstein
Partner - Creator
Partner - Creator
Author

What about taking this down to the minute lvl?

swuehl
MVP
MVP

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);