Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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,
Excellent, there are two ways to go about here:
What do you think about these ideas?
Regards,
Yes that should do it thanks, I will try it out...thanks @ArnadoSandoval been a great help..
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,
Thanks so much you have really helped me out. @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;
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;
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?
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:
Once again, the question is how to handle those arriving between 10:45 and 16:00?
Regards,
The logic is based on the firstscan of the day..does that make sense? See picture
Excellent, there are two ways to go about here:
What do you think about these ideas?
Regards,
Yes that should do it thanks, I will try it out...thanks @ArnadoSandoval been a great help..