Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
krmvacar
Creator II
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)
1 Solution

Accepted Solutions
vincent_ardiet_
Specialist
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)
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;

View solution in original post

3 Replies
Vegar
MVP
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...

krmvacar
Creator II
Creator II
Author

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

vincent_ardiet_
Specialist
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)
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;