Announcements
cancel
Showing results for
Did you mean:
Creator II

## Allocate employee working hours on a minute basis

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
Labels (1)
• ### General Question

1 Solution

Accepted Solutions
Specialist

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)

Left Join (tmpTimeTable) IntervalMatch(DateTime,Order_ID)

Drop Table WorkTable,BreakTable;

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

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

Drop Table tmpTimeTable;

3 Replies
MVP

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...

Creator II
Author

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

Specialist

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)

Left Join (tmpTimeTable) IntervalMatch(DateTime,Order_ID)

Drop Table WorkTable,BreakTable;

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

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

Drop Table tmpTimeTable;