topic Re: Worked hours for a day in QlikView

Worked hours for a day

Anonymous — Fri, 07 Mar 2014 07:57:24 GMT

Hallo,

I have a record with these fields:

- Employee nbr.

- Date/time start

- Date/time finish

I have to make a qw pivot table with the days on the x-axe, the employee nbr on the y-axe and show the worked hour for a day/employee.

The problem is that the start date and the finish date could be not the same (e.g. from 22:00 of March 7th to 06:00 of March 8th), so the right hours-for-a-day should be: 2 on March 7th and 6 on March 8th, but I'm not able to show them in a qw pivot table, bacause I have them in only record and not in two.

I Hope the problem is clear 🙂

Can anybody help me to find a way to solve this riddle?

Thank you.

nick.

Re: Worked hours for a day

sbaldwin — Fri, 07 Mar 2014 08:05:54 GMT

Hi, Nick, look at the intervalmatch script function, this will allow you to join or associate each day between day between the start and the end then calculate the number of hours, or easier if a shift only went over a single day boundary for the records going over the midnight boundary just load a table which concatenates the start date with the hours from the start till midnight then the end date with the hours from midnight to the end (then add on the records which just exist in a single day. ie the table

EMP START END

A 01/05/2005 22:00 02/05/2005 03:00

B 02/05/2005 05:00 02/05/2005 10:00

would become

EMP DATE hours

A 01/05/2005 2

A 02/05/2005 3

B 02/05/2005 5

once you have the table in this structure the pivot table should be straght forward.

Thanks

Steve

Re: Worked hours for a day

Anonymous — Fri, 07 Mar 2014 14:30:51 GMT

Hi Steve,

I wasn't able to use the INTERVALMATCH function, but I get your 2nd suggestion and I solve the problem with a UNION ALL into the SELECT statement.

Thank you very much for your reply.

Bye.

Nick.