Determine what shift an employee is on using Activity Date Time

[andymayo]

ActionDateTime	Employee
02/04/2020 08:03	JONES
02/04/2020 08:13	JONES
02/04/2020 15:38	JONES
02/04/2020 16:23	JONES
02/04/2020 11:00	SMITH
02/04/2020 11:07	SMITH
02/04/2020 11:05	SMITH
02/04/2020 18:05	SMITH
03/04/2020 07:45	JONES
03/04/2020 13:58	SMITH

I have Shift Times as Follows

Monday- Thursday the Day Shift Works 

07:30 to 16:00

on a Friday they work 07:30 - 14:30

The Lates Shift Works

10:45am to 20:30 Mon- Thurs and do not work Fridays

I wanted to create a column in my load script called SHIFT and it will either be 'DAY' or 'LATE'  using the Max and Min ActionDateTime each day for each worker determine what shift they are on.

For example if it is Mon-Thurs and the Min(ActionDateTime) is between 07:30 - 10:45 then they are on the Day Shift if the Day is Mon-Thurs Min(ActionDateTime) is between 10:45 - noon then they are on the Late Shift. 

The shift a person is on can change daily and I want to use this calcualtion throught my application not just in an Expression. I want to use it in filters etc..I want to look at lapsed time between first scan and shift start time and last scan and shift end time

Please can you advise. 

[ArnadoSandoval · Accepted solution]

Hi @andymayo 

Interesting IntervalMatch challenge, the ShiftType assignment is driven by the WeekDay and Time, therefor we need to combine the weekday and time; Qlik Sense defaut day of the week number is: 1=Mon, 2=Tue, 3=Wed, 4=Thu and 5=Fri, and the fraction of the time is a number less than 1; so if we add: Day_Number + Fraction_of_the_time we have a unique number for each day of the week, like shown below:

Now the shift's hours are identifying the day of the week as well. We do exactly the same with ActionDateTime by creating a 'time' calculated as DayofWeek(ActionDateTime) + Frac(Time(ActionDateTime), this will allow the IntervalMatch to pickup the appropiated ShiftType Record, as shown below.

Some of the columns appearing on these screenshots were used to find the Shift Type, you do not need them in the final table, I did not remove them so you can follow the script logic, I used for debugging, but once it is done, it is fine to drop them; now the script.

NoConcatenate 

Shifts:
Load *,
    WeekDay + Frac(StartTime) as wrk_ST,
    WeekDay + Frac(EndTime)   as wrk_ET
;

Load * Inline [
StartTime, EndTime, WeekDay, Shift
07:30, 16:00, 1, Day
07:30, 16:00, 2, Day
07:30, 16:00, 3, Day
07:30, 16:00, 4, Day
07:30, 14:30, 5, Day
10:45, 20:30, 1, Late
10:45, 20:30, 2, Late
10:45, 20:30, 3, Late
10:45, 20:30, 4, Late
];

Map_WeekDay:
Mapping Load 
	wrk_ST &'|'& wrk_ET,
    WeekDay
Resident Shifts;

Map_Shift:
Mapping Load 
	wrk_ST &'|'& wrk_ET,
    Shift
Resident Shifts;

NoConcatenate 

workdata_01:
Load  *,
	  1 * WeekDay(ActionDateTime)         As DayNo,
      WeekDay(ActionDateTime)             As Day,
      Date(Floor(ActionDateTime))         As Date,
      Time(Frac(ActionDateTime), 'hh:mm') As Time,
      (1 * WeekDay(ActionDateTime)) + Frac(ActionDateTime) As wrk_Time
;

Load TimeStamp#(ActionDateTime, 'DD/MM/YYYYY hh:mm') as ActionDateTime, Employee Inline [
ActionDateTime,Employee
30/03/2020 08:30, SMITH
02/04/2020 08:03, JONES
02/04/2020 08:13, JONES
02/04/2020 15:38, JONES
02/04/2020 16:23, JONES
02/04/2020 11:00, SMITH
02/04/2020 11:07, SMITH
02/04/2020 11:05, SMITH
02/04/2020 18:05, SMITH
03/04/2020 07:45, JONES
03/04/2020 13:58, SMITH
];

Map_Minwrk_Time:
Mapping Load 
    Date &'|'& Employee  As MT_Key,
    Min(wrk_Time)            As Minwrk_Time
Resident workdata_01
Group By
    Date &'|'& Employee;

NoConcatenate

workdata_02:
Load *,
     ApplyMap('Map_Minwrk_Time', Date &'|'& Employee) As Minwrk_Time
Resident workdata_01;
Left Join
IntervalMatch (wrk_Time) Load "wrk_ST" As ST, "wrk_ET" As ET Resident Shifts;

NoConcatenate 

ActivityDateTime:
Load
	ActionDateTime, 
    Employee,
    DayNo,
    Day,
    Date,
    Time,
    wrk_Time,
    Minwrk_Time,
    ApplyMap('Map_WeekDay', ST &'|'& ET) As Week_Day,
    ApplyMap('Map_Shift',   ST &'|'& ET) As Shift_Type
Resident workdata_02;

Drop Table workdata_01; 
Drop Table workdata_02;

Hope this helps,

[andymayo]

Thanks so much you have really helped me out. @ArnadoSandoval 

[andymayo]

Hi @ArnadoSandoval

Could you give me some further help please?  I have implemented the solution and I am having an issue. I am getting duplicate data if the ActivityDateTime at a time that crosses over into the second shift the late shift. I need to check that the minwrk_Time determines the shift. So I am getting Day and Late for a single employee on a single day. See screen shot. and code

Thanks

Code Below

NoConcatenate

Shifts:
Load *,
WeekDay + Frac(Timestamp#(StartTime,'hh:mm:ss')) as wrk_ST,
WeekDay + Frac(Timestamp#(EndTime,'hh:mm:ss')) as wrk_ET
;

Load * Inline [
StartTime, EndTime, WeekDay, Shift
07:00:00, 16:00:00, 1, Day
07:00:00, 16:00:00, 2, Day
07:00:00, 16:00:00, 3, Day
07:00:00, 16:00:00, 4, Day
07:00:00, 16:00:00, 5, Day
07:00:00, 16:00:00, 6, Day
07:00:00, 16:00:00, 0, Day
10:30:00, 21:30:00, 1, Late
10:30:00, 21:30:00, 2, Late
10:30:00, 21:30:00, 3, Late
10:30:00, 21:30:00, 4, Late
10:30:00, 21:30:00, 5, Late
10:30:00, 21:30:00, 6, Late
10:30:00, 21:30:00, 0, Late
];

Map_WeekDay:
Mapping Load
wrk_ST &'|'& wrk_ET,
WeekDay
Resident Shifts;

Map_Shift:
Mapping Load
wrk_ST &'|'& wrk_ET,
Shift
Resident Shifts;

NoConcatenate

workdata_01:
Load *,
1 * WeekDay(SActionDateTime) As SDayNo,
WeekDay(SActionDateTime) As SDay,
Date(Floor(SActionDateTime)) As SDate,
Time(Frac(SActionDateTime), 'hh:mm:ss') As STime,
//(1 * WeekDay(SActionDateTime)) + Frac(ActionDateTime) As wrk_Time,
(1* WeekDay(SActionDateTime) + Time(Frac(SActionDateTime), 'hh:mm:ss')) as wrk_Time;
Load
SActionDateTime as SActionDateTime,
Picker_Surname as SEmployee
Resident MI_Report;

Map_Minwrk_Time:
Mapping Load
SDate &'|'& SEmployee As MT_Key,
Min(wrk_Time) As Minwrk_Time
Resident workdata_01
Group By
SDate &'|'& SEmployee;

NoConcatenate

workdata_02:
Load *,
ApplyMap('Map_Minwrk_Time', SDate &'|'& SEmployee) As Minwrk_Time
Resident workdata_01;

Left Join
IntervalMatch (Minwrk_Time)
Load
"wrk_ST" As ST,
"wrk_ET" As ET
Resident Shifts;

NoConcatenate

ActivityDateTime:
Load
SActionDateTime,
SEmployee,
SDayNo,
SDay,
SDate,
STime,
wrk_Time,
Minwrk_Time,
ST &'|'& ET as [val1],
ApplyMap('Map_WeekDay', ST &'|'& ET) As Week_Day,
ApplyMap('Map_Shift', ST &'|'& ET) As Shift_Type
Resident workdata_02;

Drop Table workdata_01;
Drop Table workdata_02;

[ArnadoSandoval]

Hi @andymayo 

Indeed that's the case, anyway I made some changes to the script, it is now taking the minimum ActionDataTime by Employee and Date from  the Activity Record; this reduces the amount of record to process by the script down the road;  the workdata_03 table contains a reduced set of records, as multiples events for an employee on a single date were removed; the logic flows as before, the workdata_04 table basically applies some mapping before finding those employees with conflicting shifts on a single day, that is done with the AutoNumber() function, it counts the shifts per employee-day (ShiftCount), finally we keep the records having a ShiftCount of 1; note ShiftCount is equal to 1 for Employees having the Day-Late conflict.

NoConcatenate 

Shifts:
Load *,
    WeekDay + Frac(StartTime) as wrk_ST,
    WeekDay + Frac(EndTime)   as wrk_ET
;

Load * Inline [
StartTime, EndTime, WeekDay, Shift
07:30, 16:00, 1, Day
07:30, 16:00, 2, Day
07:30, 16:00, 3, Day
07:30, 16:00, 4, Day
07:30, 14:30, 5, Day
10:45, 20:30, 1, Late
10:45, 20:30, 2, Late
10:45, 20:30, 3, Late
10:45, 20:30, 4, Late
];

Map_WeekDay:
Mapping Load 
	wrk_ST &'|'& wrk_ET,
    WeekDay
Resident Shifts;

Map_Shift:
Mapping Load 
	wrk_ST &'|'& wrk_ET,
    Shift
Resident Shifts
Order By Shift;

NoConcatenate 

workdata_03:
Load TimeStamp#(ActionDateTime, 'DD/MM/YYYYY hh:mm') as ActionDateTime, Employee Inline [
ActionDateTime,Employee
30/03/2020 08:30, SMITH
01/04/2020 18:03, JONES
01/04/2020 19:03, JONES
02/04/2020 08:03, JONES
02/04/2020 08:13, JONES
02/04/2020 15:38, JONES
02/04/2020 16:23, JONES
02/04/2020 11:00, SMITH
02/04/2020 11:07, SMITH
02/04/2020 16:07, JOE
02/04/2020 11:05, SMITH
02/04/2020 18:05, SMITH
03/04/2020 07:45, JONES
03/04/2020 13:58, SMITH
];

NoConcatenate

workdata_03_01:
Load Employee             As wrk_Employee,
     Date(Floor(ActionDateTime), 'DD/MM/YYYY') As wrk_date,
     Min(ActionDateTime)  As wrk_Earliest
Resident workdata_03
Group By
     Employee,
     Date(Floor(ActionDateTime), 'DD/MM/YYYY');

Drop Table workdata_03;

NoConcatenate

workdata_03:
Load Date(wrk_Earliest,'DD/MM/YYYY hh:mm') As ActionDateTime,
     wrk_Employee As Employee
Resident workdata_03_01
Order By wrk_Earliest;

Drop Table workdata_03_01;

NoConcatenate

workdata_01:
Load  *,
	  1 * WeekDay(ActionDateTime)         As DayNo,
      WeekDay(ActionDateTime)             As Day,
      Date(Floor(ActionDateTime))         As Date,
      Time(Frac(ActionDateTime), 'hh:mm') As Time,
      (1 * WeekDay(ActionDateTime)) + Frac(ActionDateTime) As wrk_Time
;
Load TimeStamp#(ActionDateTime, 'DD/MM/YYYYY hh:mm') as ActionDateTime, Employee
Resident workdata_03;

Map_Minwrk_Time:
Mapping Load 
    Date &'|'& Employee  As MT_Key,
    Min(wrk_Time)        As Minwrk_Time
Resident workdata_01
Group By
    Date &'|'& Employee;

NoConcatenate

workdata_02:
Load Distinct *,
     ApplyMap('Map_Minwrk_Time', Date &'|'& Employee) As Minwrk_Time
Resident workdata_01;
Left Join
IntervalMatch (wrk_Time) Load "wrk_ST" As ST, "wrk_ET" As ET Resident Shifts;

NoConcatenate

workdata_04:
Load
	ActionDateTime, 
    Employee,
    DayNo,
    Day,
    Date,
    Time,
    wrk_Time,
    Minwrk_Time,
    ApplyMap('Map_WeekDay', ST &'|'& ET) As Week_Day,
    ApplyMap('Map_Shift',   ST &'|'& ET) As Shift_Type
Resident workdata_02;

Map_EmpDateShift:
Mapping Load Employee &'|'& Date &'|'& Shift_Type As Key,
     AutoNumber(Employee &'|'&Date&'|'&Shift_Type, Employee &'|'&Date) As ShiftCount
Resident workdata_04
Order By Employee, Date, Shift_Type;

NoConcatenate

ActivityDateTime:
Load *
Where ShiftCount = 1;

Load *,
     ApplyMap('Map_EmpDateShift', Employee &'|'& Date &'|'& Shift_Type) As ShiftCount
Resident workdata_04;

Drop Table workdata_01; 
Drop Table workdata_02;
Drop Table workdata_03;
Drop Table workdata_04;

[andymayo]

Hi @ArnadoSandoval 

That is great to get rid of the duplicates. Just the last issue which is picking the incorrect shift if the first scan falls within both shifts example attached. First scan 11:58am (THis should be Late but it calculates it as Day) This is between the shift Day (07:00am to 6pm) and Late Shift (10:304m-9:30pm)  and the first scan fall in both.  If the min ActionDateTime is greater than the StartTime of the Late Shift it is "Late" if minActionDateTime is less than  Start Time of the Late shift....it should be a "Day" shift...not sure how to do that, can you help? So I guess the IntervalMatch need changing?

[ArnadoSandoval]

@andymayo 

I would like to understand the business rules for your Day and Late shift, I understand that in real life, the day shift goes between 07:30 and 16:00, and the late shift happen between 10:45 and 20:30, that is real-life; Now, I want to understand the logical interpretation, I think we agree that anybody showing between 07:40 and 10:45 arrived in the Day shift, somebody else arriving after 16:00 is on the Late shift, that is crystal clear, now my question is: What about those people showing up between 10:45 and 16:00, Are they in the Day shift? or They belong to the Late shift; What is the rule defining this time-frame.

The current rules are:

• Day Shift: Anybody arriving before 07:30-16:00 (including those arriving after 10:45)
• Late Shift: Anybody arriving after 16:00

Once again, the question is how to handle those arriving between 10:45 and 16:00?

Regards,

[andymayo]

@ArnadoSandoval 

The logic is based on the firstscan of the day..does that make sense? See picture

[ArnadoSandoval · Accepted solution]

@andymayo 

Excellent, there are two ways to go about here:

• Introduce a logical end of the day shift at 10:44:59 by editing the EndTime data to 10:44 of Day Shifts, except Fridays, as Fridays do not have Late shift; If you do this, the logic already in place will work.
• If the physical end of the day shift is important, add an extra column to record the physical end of the shift, and enter the corresponding times for both, day and later shifts.

What do you think about these ideas?

Regards,

[andymayo · Accepted solution]

Yes that should do it thanks, I will try it out...thanks @ArnadoSandoval  been a great help..