Allocate employee working hours on a minute basis

[krmvacar]

Hi @all

ı have a two tables. The first table shows the order-based working hours of the staff.

The second table shows the breaks taken by the staff during their working hours.

We calculate the minutes of breaks as minus

Here, I want to see the working and stopping times of the staff on a minute basis, from the minute they start working to the minute they finish the last job. Is this possible?

Thanks

Best Regards

First Table:

Personel_work_start	Personel_work_end	Order_ID
30.10.2023 08:05	30.10.2023 08:19	11111111
30.10.2023 08:09	30.10.2023 08:40	22222222
30.10.2023 08:23	30.10.2023 08:30	33333333
30.10.2023 08:50	30.10.2023 09:25	44444444

Second_table:

Personel_break_start	Personel_break_end	Order_ID
30.10.2023 08:21	30.10.2023 08:31	22222222
30.10.2023 08:58	30.10.2023 09:09	44444444
30.10.2023 09:14	30.10.2023 09:25	44444444

I want to result:

Date	Order_ID	Order_ID	Order_ID	Order_ID	Order_ID	Order_ID
	11111111	22222222	22222222	33333333	44444444	44444444
30.10.2023 08:05	1
30.10.2023 08:06	1
30.10.2023 08:07	1
30.10.2023 08:08	1
30.10.2023 08:09	1	1
30.10.2023 08:10	1	1
30.10.2023 08:11	1	1
30.10.2023 08:12	1	1
30.10.2023 08:13	1	1
30.10.2023 08:14	1	1
30.10.2023 08:15	1	1
30.10.2023 08:16	1	1
30.10.2023 08:17	1	1
30.10.2023 08:18	1	1
30.10.2023 08:19	1	1
30.10.2023 08:20		1
30.10.2023 08:21		1	-1
30.10.2023 08:22		1	-1
30.10.2023 08:23		1	-1	1
30.10.2023 08:24		1	-1	1
30.10.2023 08:25		1	-1	1
30.10.2023 08:26		1	-1	1
30.10.2023 08:27		1	-1	1
30.10.2023 08:28		1	-1	1
30.10.2023 08:29		1	-1	1
30.10.2023 08:30		1	-1	1
30.10.2023 08:31		1	-1
30.10.2023 08:32		1
30.10.2023 08:33		1
30.10.2023 08:34		1
30.10.2023 08:35		1
30.10.2023 08:36		1
30.10.2023 08:37		1
30.10.2023 08:38		1
30.10.2023 08:39		1
30.10.2023 08:40		1
30.10.2023 08:41
30.10.2023 08:42
30.10.2023 08:43
30.10.2023 08:44
30.10.2023 08:45
30.10.2023 08:46
30.10.2023 08:47
30.10.2023 08:48
30.10.2023 08:49
30.10.2023 08:50					1
30.10.2023 08:51					1
30.10.2023 08:52					1
30.10.2023 08:53					1
30.10.2023 08:54					1
30.10.2023 08:55					1
30.10.2023 08:56					1
30.10.2023 08:57					1
30.10.2023 08:58					1	-1
30.10.2023 08:59					1	-1
30.10.2023 09:00					1	-1
30.10.2023 09:01					1	-1
30.10.2023 09:02					1	-1
30.10.2023 09:03					1	-1
30.10.2023 09:04					1	-1
30.10.2023 09:05					1	-1
30.10.2023 09:06					1	-1
30.10.2023 09:07					1	-1
30.10.2023 09:08					1	-1
30.10.2023 09:09					1	-1
30.10.2023 09:10					1
30.10.2023 09:11					1
30.10.2023 09:12					1
30.10.2023 09:13					1
30.10.2023 09:14					1	-1
30.10.2023 09:15					1	-1
30.10.2023 09:16					1	-1
30.10.2023 09:17					1	-1
30.10.2023 09:18					1	-1
30.10.2023 09:19					1	-1
30.10.2023 09:20					1	-1
30.10.2023 09:21					1	-1
30.10.2023 09:22					1	-1
30.10.2023 09:23					1	-1
30.10.2023 09:24					1	-1
30.10.2023 09:25					1	-1

[Vegar]

If you generate a transaction table with one row per minute, then you should be able to associate the two sets of  intervals with the minutes using intervalmatch.

https://help.qlik.com/sv-SE/sense/August2023/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefix...

[krmvacar]

I tried but I couldn't get a minute-based result. Is it possible for you to help?

[vincent_ardiet_ · Accepted solution]

Something like this:

WorkTable:
Load TimeStamp#(Personel_work_start,'DD.MM.YYYY hh:mm') as Personel_work_start,TimeStamp#(Personel_work_end,'DD.MM.YYYY hh:mm') as Personel_work_end,Order_ID inline
[Personel_work_start,Personel_work_end,Order_ID
30.10.2023 08:05,30.10.2023 08:19,11111111
30.10.2023 08:09,30.10.2023 08:40,22222222
30.10.2023 08:23,30.10.2023 08:30,33333333
30.10.2023 08:50,30.10.2023 09:25,44444444];

BreakTable:
Load TimeStamp#(Personel_break_start,'DD.MM.YYYY hh:mm') as Personel_break_start,TimeStamp#(Personel_break_end,'DD.MM.YYYY hh:mm') as Personel_break_end,Order_ID inline
[Personel_break_start,Personel_break_end,Order_ID
30.10.2023 08:21,30.10.2023 08:31,22222222
30.10.2023 08:58,30.10.2023 09:09,44444444
30.10.2023 09:14,30.10.2023 09:25,44444444];

tmpTimes:
Load Min(TimeStamp(FieldValue('Personel_work_start',RecNo()))) as Time AutoGenerate FieldValueCount('Personel_work_start') ;
Load Min(TimeStamp(FieldValue('Personel_break_start',RecNo()))) as Time AutoGenerate FieldValueCount('Personel_break_start') ;
Load Max(TimeStamp(FieldValue('Personel_work_end',RecNo()))) as Time AutoGenerate FieldValueCount('Personel_work_end') ;
Load Max(TimeStamp(FieldValue('Personel_break_end',RecNo()))) as Time AutoGenerate FieldValueCount('Personel_break_end') ;

tmpMinMaxTImes:
Load Min(Time) as MinStart,Max(Time) as MaxEnd Resident tmpTimes;

LET minStart=num(Peek('MinStart'));
LET maxEnd=num(Peek('MaxEnd'));

Drop Tables tmpTimes, tmpMinMaxTImes;

tmpTimeTable:
Load Timestamp($(minStart)+(RowNo()-1)/1440) as DateTime AutoGenerate ($(maxEnd)-$(minStart))*1440;

Inner Join (tmpTimeTable)
Load Num(FieldValue('Order_ID',RecNo())) as Order_ID AutoGenerate FieldValueCount('Order_ID') ;

Left Join (tmpTimeTable) IntervalMatch(DateTime,Order_ID)
Load Personel_work_start,Personel_work_end,Order_ID Resident WorkTable;

Left Join (tmpTimeTable) IntervalMatch(DateTime,Order_ID)
Load Personel_break_start,Personel_break_end,Order_ID Resident BreakTable;

Drop Table WorkTable,BreakTable;

AllocationTable:
Load
DateTime
,Order_ID
,'Work' as Status
,1 as Value
Resident tmpTimeTable
Where Not IsNull(Personel_work_start);

Concatenate(AllocationTable)
Load
DateTime
,Order_ID
,'Break' as Status
,-1 as Value
Resident tmpTimeTable
Where Not IsNull(Personel_break_start);

Drop Table tmpTimeTable;