Attendance Occurrence Count

Hi, @John-Peddle,

This is a fairly complex bit of logic you're trying to implement. Let me take another swing at explaining better. You can always reach out to your CSM or AE for more in-depth direction than I'll be able to give here. Please DM me if you need help contacting your CSM or AE.

Let me address your questions in reverse order:

1) "Explode the names across dates": What I mean here is join the employee table with the dates table. Depending on the format of your absentee dataset, this may or may not be necessary. What you want is a table something like this, where there are no gaps in the dates (let's call this the date_employee_status table):

Date       |EmployeeID |EmployeeStatus
-----------|-----------|------------------
2018-10-12 |1          |Present
2018-12-11 |1          |Absent
2018-12-10 |1          |Present
2018-12-12 |2          |Absent
2018-12-11 |2          |Absent
2018-12-10 |2          |Present
2018-12-12 |3          |Absent
2018-12-11 |3          |Present
2018-12-10 |3          |Absent

2) Need for a separate calendar: Again, you may or may not need this depending on the format of your absentee dataset. What you're driving toward is a dataset like I'm showing above

Here is where it becomes a bit more difficult. What you want to do is look at each employee on each day and determine if the employee:

• Was present
• Was absent
• Was absent the day before

So, you need to know the status of each employee on each day, but you also need to know the status of each employee on the day before.

There are probably many ways of doing this. From a coding perspective, the MySQL method I cited yesterday is more complex, but from the logical perspective, it is more straightforward. Another option is to us a Magic ETL dataflow. That method requires no code but is more complex from a logical perspective. Whatever method you use, you need to be able to look at each employee on each day AND the day before. That's the key.

You could do this using Magic ETL using a Rank & Window tile, using a Row Number function, ordering by Date, and partitioning by EmployeeID. The result will be a new column where each date for each employee is numbered. Something like this:

Date       |EmployeeID |EmployeeStatus |EmployeeDateNumber
-----------|-----------|---------------|--------------------
2018-10-12 |1          |Present        |3
2018-12-11 |1          |Absent         |2
2018-12-10 |1          |Present        |1
2018-12-12 |2          |Absent         |3
2018-12-11 |2          |Absent         |2
2018-12-10 |2          |Present        |1
2018-12-12 |3          |Absent         |3
2018-12-11 |3          |Present        |2
2018-12-10 |3          |Absent         |1

Then, using a Calculator tile, create a new column as EmployeeDateNumber + 1 and call this new column NextEmployeeDateNumber.

Finally, you can join that dataset to itself where EmployeeID = EmployeeID and EmployeeDateNumber = NextEmployeeDateNumber

Having performed that join, you'll have a single dataset where you have Date, EmployeeID, EmployeeStatus on that date, and EmpoyeeStatus from the day before. Then, just apply logic to determine if an employee was absent on a date and if that absence in new (first day) or continuing.

Hope that is a little more helpful. 

Best,
Dan