Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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);