topic Time Slot Analysis in QlikView

Time Slot Analysis

Wed, 17 Aug 2016 15:35:50 GMT

Hi community ,

i have a new challenge in my script, and i can't figure out the solution.

I have a big table with "work hours" . It's something like that:

ID_WORKER DATE ENTRY_TIMESTAMP EXIT_TIMESTAMP sum( WORKHOURS)

000001 2016/08/15 08.00 12.30 4.5

The last field (WORKHOURS)is obtained in the script with:

WORKHOURS = (EXIT_TIMESTAMP - ENTRY_TIMESTAMP ) *60*24

My goal is to obtain the following Time Slot Analysis :

How can i reach my goal?

Thanks all

Gysbert_Wassenaar — Thu, 18 Aug 2016 08:18:50 GMT

Perhaps something like this

LOAD

ID_WORKER,

DATE,

ENTRY_TIMESTAMP,

EXIT_TIMESTAMP,

'FROM ' & Time(ENTRY_TIMESTAMP + (IterNo()-1)/24,'hh.mm') &

' to ' & Time(ENTRY_TIMESTAMP + (IterNo())/24,'hh.mm') AS TimeSlots

If( ENTRY_TIMESTAMP + (IterNo())/24 <= EXIT_TIMESTAMP, 1,

EXIT_TIMESTAMP - ENTRY_TIMESTAMP + (IterNo()-1)/24 * 60 * 24) AS WORKHOURS

FROM

...source data...

WHILE

ENTRY_TIMESTAMP + (IterNo()-1)/24 <= EXIT_TIMESTAMP

;

Thu, 18 Aug 2016 13:44:18 GMT

Thanks Gysbert.