Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
blunckc1
Creator
Creator
‎2017-09-27 04:32 AM

New record per 15 minute interval

Hey everyone,

I am trying to create a chart that shows how many employees are working per 15 minute interval.

My initial thought to solve this is to run a loop over it so a new record gets created for each 15 min gap between an employees shift start and end time.

For example - Incoming data looks like this:

PID

PAN

ShiftStartDateTime

ShiftEndDateTime

00176263

00176263

27/09/17 09:00

27/09/17 12:00

001762630017626327/09/17 12:0027/09/17 16:06

Output data would look like this:

PID

PAN

ShiftStartDateTime

ShiftEndDateTime

ShiftDateHour

00176263

00176263

27/09/17 09:00

27/09/17 12:00

27/09/17 09:00

00176263

00176263

27/09/17 09:00

27/09/17 12:00

27/09/17 09:15

00176263

00176263

27/09/17 09:00

27/09/17 12:00

27/09/17 09:30

00176263

00176263

27/09/17 09:00

27/09/17 12:00

27/09/17 09:45

00176263

00176263

27/09/17 09:00

27/09/17 12:00

27/09/17 10:00

00176263

00176263

27/09/17 09:00

27/09/17 12:00

27/09/17 10:15

00176263

00176263

27/09/17 09:00

27/09/17 12:00

27/09/17 10:30

00176263

00176263

27/09/17 09:00

27/09/17 12:00

27/09/17 10:45

00176263

00176263

27/09/17 09:00

27/09/17 12:00

27/09/17 11:00

00176263

00176263

27/09/17 09:00

27/09/17 12:00

27/09/17 11:15

00176263

00176263

27/09/17 09:00

27/09/17 12:00

27/09/17 11:30

00176263

00176263

27/09/17 09:00

27/09/17 12:00

27/09/17 11:45

00176263

00176263

27/09/17 12:00

27/09/17 16:06

27/09/17 12:00

001762630017626327/09/17 12:0027/09/17 16:0627/09/17 12:15
001762630017626327/09/17 12:0027/09/17 16:0627/09/17 12:30
001762630017626327/09/17 12:0027/09/17 16:0627/09/17 12:45
001762630017626327/09/17 12:0027/09/17 16:0627/09/17 13:00
001762630017626327/09/17 12:0027/09/17 16:0627/09/17 13:15
001762630017626327/09/17 12:0027/09/17 16:0627/09/17 13:30
001762630017626327/09/17 12:0027/09/17 16:0627/09/17 13:45
001762630017626327/09/17 12:0027/09/17 16:0627/09/17 14:00
001762630017626327/09/17 12:0027/09/17 16:0627/09/17 14:15
001762630017626327/09/17 12:0027/09/17 16:0627/09/17 14:30
001762630017626327/09/17 12:0027/09/17 16:0627/09/17 14:45
001762630017626327/09/17 12:0027/09/17 16:0627/09/17 15:00
001762630017626327/09/17 12:0027/09/17 16:0627/09/17 15:15
001762630017626327/09/17 12:0027/09/17 16:0627/09/17 15:30
001762630017626327/09/17 12:0027/09/17 16:0627/09/17 15:45
001762630017626327/09/17 12:0027/09/17 16:0627/09/17 16:00

I've been able to do this at hourly intervals, but completely stumped at how to do it at 15 minute intervals + have it round down to nearest interval.  Start and end times can be at every minute of the day so the rounding will be an important aspect so there aren't any duplicate records.

Here is the script I used to achieve the hourly intervals, this wasn't great as it was rounding up to the next hour and for some reason, it missed hours for some employees...

WorkbrainData:


LOAD PID,


     PAN,


     ShiftStartDateTime,


     ShiftEndDateTime,


     timestamp(floor(ShiftStartDateTime + iterno()/24, 1/24)) as ShiftDateHour


Resident WORKEDWorkbrainData



While ShiftStartDateTime + (IterNo()-1)/24 <= ShiftEndDateTime;

Thanks in advance for the assist!!

Cheers

Carl

1,984 Views
0 Likes
1 Solution

Accepted Solutions
sunny_talwar
MVP
MVP
‎2017-09-28 04:23 AM

In response to blunckc1

Hopefully this will do it

Table:

LOAD *,

TimeStamp(If(Frac(ShiftStartDateTime*1440/15) = 0, Ceil(ShiftStartDateTime + ((IterNo() - 1) * MakeTime(0, 14, 59)), 15/1440), Ceil(ShiftStartDateTime + ((IterNo()-2) * MakeTime(0, 15, 1)), 15/1440))) as ShiftDateHour

While Ceil(ShiftStartDateTime + ((IterNo() - 1) * MakeTime(0, 15, 1)), 15/1440) <= Ceil(ShiftEndDateTime, 15/1440);

LOAD * INLINE [

    PID, PAN, ShiftStartDateTime, ShiftEndDateTime

    00176263, 00176263, 27/09/17 09:00, 27/09/17 12:00

    00176264, 00176264, 27/09/17 12:00, 27/09/17 16:06

    00176265, 00176265, 27/09/17 12:06, 27/09/17 16:06

];

View solution in original post

276050_v3.qvw
1,824 Views
0 Likes
11 Replies
sunny_talwar
MVP
MVP
‎2017-09-27 04:42 AM

Try this

WorkbrainData: 

LOAD PID, 

     PAN, 

     ShiftStartDateTime, 

     ShiftEndDateTime, 

     TimeStamp(Ceil(ShiftStartDateTime + (IterNo() - 1) * 15/1440, 1/1440)) as ShiftDateHour

Resident WORKEDWorkbrainData  

While Ceil(ShiftStartDateTime + (IterNo() - 1) * 15/1440, 1/1440) <= Ceil(ShiftEndDateTime, 1/1440);

Tried with this sample script

Table:

LOAD *,

TimeStamp(Ceil(ShiftStartDateTime + (IterNo() - 1) * 15/1440, 1/1440)) as ShiftDateHour

While Ceil(ShiftStartDateTime + (IterNo() - 1) * 15/1440, 1/1440) <= Ceil(ShiftEndDateTime, 1/1440);

LOAD * INLINE [

    PID, PAN, ShiftStartDateTime, ShiftEndDateTime

    00176263, 00176263, 27/09/17 09:00, 27/09/17 12:00

];

276050.qvw
1,475 Views
0 Likes
blunckc1
Creator
Creator
‎2017-09-27 04:58 AM
Author

In response to sunny_talwar

Thanks Sunny!  That works great.  Is there any way you know of that can have the output table end at 11:45?

My logic is that each interval represents someone working from (for example) 11:45 to 12:00.  Because the shift ends at 12:00, there shouldn't be a ShiftDateHour of 12:00 because the person isn't working between 12:00 and 12:15.

Thanks

Carl

1,478 Views
0 Likes
sunny_talwar
MVP
MVP
‎2017-09-27 05:00 AM

In response to blunckc1

Change this

While Ceil(ShiftStartDateTime + (IterNo() - 1) * 15/1440, 1/1440) <= Ceil(ShiftEndDateTime, 1/1440);

to

While Ceil(ShiftStartDateTime + (IterNo() - 1) * 15/1440, 1/1440) < Ceil(ShiftEndDateTime, 1/1440);

Removed the = from <=

1,478 Views
0 Likes
antoniotiman
Master III
Master III
‎2017-09-27 05:06 AM

Hi Carl,

may be

LOAD PID, 
PAN, 
ShiftStartDateTime, 
ShiftEndDateTime, 
timestamp(ShiftStartDateTime + (iterno()-1)*15/1440) as ShiftDateHour 
Resident WORKEDWorkbrainData 
While ShiftStartDateTime + (iterno()-1)*15/1440 <= ShiftEndDateTime;

Regards,

Antonio

1,478 Views
0 Likes
blunckc1
Creator
Creator
‎2017-09-27 06:12 AM
Author

In response to sunny_talwar

Amazing.  Last problem.

Shifts that have a start time of (for example) 07:06 are breaking into increments as follows:

07:06

07:21

07:36

etc.

How can I round those back to 07:00, 07:15, 07:30.

Cheers Sunny!

1,478 Views
0 Likes
sunny_talwar
MVP
MVP
‎2017-09-27 07:11 AM

In response to blunckc1

May be like this

Table:

LOAD *,

TimeStamp(Floor(ShiftStartDateTime + ((IterNo() - 1) * MakeTime(0, 15, 1)), 15/1440)) as ShiftDateHour

While Floor(ShiftStartDateTime + ((IterNo() - 1) * MakeTime(0, 15, 1)), 15/1440) < Ceil(ShiftEndDateTime, 15/1440);

LOAD * INLINE [

    PID, PAN, ShiftStartDateTime, ShiftEndDateTime

    00176263, 00176263, 27/09/17 09:06, 27/09/17 12:06

];

276050_v1.qvw
1,478 Views
0 Likes
blunckc1
Creator
Creator
‎2017-09-27 08:19 PM
Author

In response to sunny_talwar

Morning Sunny,

That fixes the times that do not start on a 15 minute interval, but for some reason, some of the times that do start on a 15 minute interval are creating a ShiftDateHour at the interval prior.

For example:

ShiftStartDateTime of 25/04/2016 07:00 has a ShiftDateHour of 25/04/2016 06:45:00

But then others are completely fine.

ShiftStartDateTime of 25/04/2016 08:00 has a ShiftDateHour of 25/04/2016 08:00:00

Any ideas what is causing this?

Attached is some data to help.

Thanks for all your advice and help!

Carl

10156 KB
1,477 Views
0 Likes
sunny_talwar
MVP
MVP
‎2017-09-27 09:14 PM

In response to blunckc1

Try this

Table:

LOAD *,

TimeStamp(If(Frac(ShiftStartDateTime*1440/15) = 0, Ceil(ShiftStartDateTime + ((IterNo() - 1) * MakeTime(0, 15, 1)), 15/1440), Ceil(ShiftStartDateTime + ((IterNo()-2) * MakeTime(0, 15, 1)), 15/1440))) as ShiftDateHour

While If(Frac(ShiftStartDateTime*1440/15) = 0, Ceil(ShiftStartDateTime + ((IterNo() - 1) * MakeTime(0, 15, 1)), 15/1440) < Ceil(ShiftEndDateTime, 15/1440), Ceil(ShiftStartDateTime + ((IterNo() - 1) * MakeTime(0, 15, 1)), 15/1440) <= Ceil(ShiftEndDateTime, 15/1440));

LOAD * INLINE [

    PID, PAN, ShiftStartDateTime, ShiftEndDateTime

    00176263, 00176263, 27/09/17 09:00, 27/09/17 12:00

    00176264, 00176264, 27/09/17 12:00, 27/09/17 16:06

    00176265, 00176265, 27/09/17 12:06, 27/09/17 16:06

];

276050_v2.qvw
1,475 Views
blunckc1
Creator
Creator
‎2017-09-28 12:20 AM
Author

In response to sunny_talwar

Nearly there!  Small bug.

For times that start on the 15 minute interval, the "2nd" interval isn't created.  As per output below:

    

PIDPANShiftStartDateTimeShiftEndDateTimeShiftDateHour
001762630017626327/09/17 09:0027/09/17 12:0027/09/17 9:00
001762630017626327/09/17 09:0027/09/17 12:0027/09/17 9:30
001762630017626327/09/17 09:0027/09/17 12:0027/09/17 9:45

There should be a ShiftDateHour of 27/09/17 09:15 but there isn't.

Issue doesn't occur for times that do not start on a 15 min interval

    

PIDPANShiftStartDateTimeShiftEndDateTimeShiftDateHour
001762650017626527/09/17 12:0627/09/17 16:0627/09/17 12:00
001762650017626527/09/17 12:0627/09/17 16:0627/09/17 12:15
001762650017626527/09/17 12:0627/09/17 16:0627/09/17 12:30
001762650017626527/09/17 12:0627/09/17 16:0627/09/17 12:45

Cheers

Carl

1,476 Views
0 Likes