Calculate hours worked between specific times during the day

[John5]

Hi

I have run into a problem trying to calculate hours worked between specific times during the day.
I need to figure out how to calculate the number of hours employees are working between for example between 13.00 - 14.00 and 14.00 - 15.00, 15.00 - 16.00....etc.

I think I am overthinking the problem and overworking my script.

Maybe this could be done using rangemin() and rangmax() ?

Check my script in attached app. Any feedback is appreciated.

This is what my data looks like
Id, Start, End
2755, 2022-10-03 07:00, 2022-10-03 16:00
2678, 2022-10-03 08:00, 2022-10-03 16:30
2728, 2022-10-03 08:00, 2022-10-03 17:00
2727, 2022-10-03 10:00, 2022-10-03 19:00
2813, 2022-10-03 13:30, 2022-10-03 23:30
2732, 2022-10-04 07:00, 2022-10-04 12:00
2717, 2022-10-04 07:00, 2022-10-04 16:00
2810, 2022-10-04 07:00, 2022-10-04 17:00
2813, 2022-10-04 09:30, 2022-10-04 19:00

This is what I want to achive:

Hour	Total hours
0	0
1	0
2	0
3	0
4	0
5	0
6	0
7	4
8	6
9	6
10	8
11	8
12	8
13	7
14	7
15	7
16	7
17	4
18	2
19	2
20	1
21	1
22	1
23	1

I've attached a sample script.

Thanks,
John

[Oleg_Troyansky · Accepted solution]

Hi John,

You can do it with the IntervalMatch join. Load the list of 24 distinct hours and join them to the table with the start and the end timestamps using IntervalMatch. Make sure that your timestamps are loaded indeed as proper timestamps and not as strings. If necessary, use the function Timestamp() to convert.

Something like this would work:

Intervalmatch (DistinctHour)

LOAD

Start, End

resident

...

You can find a very detailed description of this technique, with hands-on exercises, in my book. Since you are using QlikView, my book would be ideal for you. See the link in my signature.

You may also consider joining us at the Masters Summit for Qlik in New Orleans on November 14-16, where you can learn many other advanced development techniques.

Cheers,