Calculating employees' shift time work

[krmvacar]

Hi All,

the employees have 3 shifts. I would like to know if the malfunctions of the employees here were caused by my shift time or if they did it afterwards.

Shıft hours mean:

1= 08:00-16:00 2=08:00-24:00 3=00:00-24:00

Table A:

work_date	Shıft_Hours
21.03.2023	1
22.03.2023	2
23.03.2023	3
24.03.2023	1
25.03.2023	1

Table B:

Peronel_number	work_date	Fault_Time_start	Fault_Time_End
312879	21.03.2023	21.03.2023 17:05	21.03.2023 19:35
987476	22.03.2023	22.03.2023 21:15	22.03.2023 219
73639	23.03.2023	23.03.2023 08:11	23.03.2023 11:15
83693	24.03.2023	24.03.2023 14:00	24.03.2023 15:00
44973	25.03.2023	25.03.2023 14:00	25.03.2023 17:00

I want to result

Result_Table:

Peronel_number	work_date	Fault_Time_start	Fault_Time_End	Fault_Time
312879	21.03.2023	21.03.2023 17:05	21.03.2023 19:35	00:00
987476	22.03.2023	22.03.2023 21:15	22.03.2023 22:19	01:04
73639	23.03.2023	23.03.2023 08:11	23.03.2023 11:15	03:04
83693	24.03.2023	24.03.2023 14:00	24.03.2023 15:00	01:00
44973	25.03.2023	25.03.2023 14:00	25.03.2023 17:00	02:00

Thank you for help.

[rwunderlich · Accepted solution]

Do you want to calc in the chart or the script? If in the chart, a clear way of expressing the rule might be:

Shıft_Hours = 3
or (Shıft_Hours = 2 and hour(Fault_Time_start) >= 😎
or ( Shıft_Hours = 1 and hour(Fault_Time_start) >= 8 and  hour(Fault_Time_start) <= 16)

This assumes you are only concerned with if the fault started in the shift. You can use this with the fault time calculation measure like:

(Fault_Time_End - Fault_Time_start)
* -(
Shıft_Hours = 3
or (Shıft_Hours = 2 and hour(Fault_Time_start) >= 😎
or ( Shıft_Hours = 1 and hour(Fault_Time_start) >= 8 and  hour(Fault_Time_start) <= 16)
)

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com