Snap | Step | Configuration | Result/Output |
---|
Snap 1 | Add and configure an Exact Online Read Snap to query Payroll - Employments data. | Filter EmployeeHID from the parent Pipeline using Filter Records table. Select ID and StartDate fields using Output Field Selection table. | All records of Employments data filtered by EmployeeHID from ExactOnline API. |
Snap 2 | Add a Mapper Snap to map the field from the step 1. | Configure the StartDate field using the expression: $StartDate.replace("/Date(","").replace(")/",""), and map it to $StartDate. | All records of mapped Employments data with reformed StartDate field. |
Snap 3 | Add and configure a new Exact Online Read Snap to query HRM - Schedules data. | Filter Employment as $groupBy.EmploymentId from the mapper of step 2 using Filter Records table. Select AverageHours fields using Output Field selection table. | All records of Schedules data filtered with employeeID from previous steps. |
Snap 4 | Add a Router Snap to distinguish if an employee has been employed for less than 5 weeks. As we are looking back on a 5 week period, it is possible that an employee has been employed for less than 5 weeks. In this case we want to compare the booked hours with the expected hours booked of this smaller period. | We use a Router Snap to distinguish between the two scenarios: if ($original.StartDate.replace("/Date(","").replace(")/","") < (Date.now().minusDays(35).getTime()) ) , route to the output0 (employee startdate >= 5 weeks) ;
if(!($original.StartDate.replace("/Date(","").replace(")/","") < (Date.now().minusDays(35).getTime()))), route to the output1 (employee startdate < 5 weeks) ; | |
Snap 5 | Add a Mapper Snap to output0 (Employee has been working 5 weeks or more) to calculate Average Hours. | Check the mapper settings in the Pipeline. Note that the Average Hours is mapped as $AverageHours*5 -> $AverageHours , as the AverageHours field from the output is an average per week, we need to multiply it by 5, to reflect the 5 weeks period. We also include the employee details and booked hours overview from the input. | Employee details data with employee hours and average hours per week. |
Snap 6,7,8 | Add three new Mapper Snaps to output1 (Employee has been working for less than 5 weeks), they are used to add current date time, calculate the contract hours and compute the average hours. | The first Mapper Snap determines the starting date of the employment and today. The second Mapper Snap calculates the number of hours in a day an employee is working (contractually) and the number of days the employee has been employed. In the line of (Math.floor(($Today-$original.StartDate) / 86400000)) - ((Math.floor(($Today-$original.StartDate) / 86400000)/7)*2) -> $DaysInService, this line of Mapper determines the date-difference between the starting date of the employee and today. Then the weekend-days are distracted to reflect the number of working days. The third Mapper Snap determines both the contract hours and the AverageHours, based on the DaysInService. In the line of $ContractHoursPerDay * $DaysInService -> $AverageHours, by this line of mapper, the contract-hours per day * the number of working days for this period, determines the number of hours an employee should have booked during the checked period (contractually) | Employee details data with employee hours, average hours per week, and contract hours. |
Snap 9 Snap 5,6,7,8 Snap 4 | Add a Join Snap () with Join Type as Merge to merge the data from mappers after output0 and output1 of Router Snap. | Joined data for previous mappers. | |
Snap 10 | Add a Mapper Snap (to map the hour types. This step groups the hours to the different statuses and calculates the contract hours (if available). | ContractHours' in $ ? ($ContractHours*5) : null -> $ContractHours indicates if an employee has contract hours, they are timed by 5 to reflect the 5 week-period. If not, the field remains null.
| Employee details data with fields of different hour types (HoursBooked, HoursDrafted, HoursRejected, AverageHours, ContractHours). |
Snap 11 | Add a Filter Snap to filter the hour types with the Filter expression $HoursBooked.HoursBooked < $AverageHours. This step filters out the employees that booked less hours over the last 5 weeks then their contract specifies. | | Employee details data with fields of different hour types (HoursBooked, HoursDrafted, HoursRejected, AverageHours, ContractHours) filtered by expression $HoursBooked.HoursBooked < $AverageHours. |
Snap 12 | Finally, add a Structure Snap () to restructure the data stream. | | Restructured employee details data with fields of different hour types (HoursBooked, HoursDrafted, HoursRejected, AverageHours, ContractHours). |