Page Comparison

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