Hi,
I have an employee biometric dataset which has a record for every In and Out of the employee.
There are times when the system does not capture one of the entries so i might end up with a In-In or Out-Out consecutively or miss out one record for the employee.
Something like this:
| Employee ID | In/Out | Time |
| 123 | In | 06-05-2016 00:00 |
| 123 | Out | 06-05-2016 16:00 |
| 123 | In | 07-05-2016 00:05 |
| 124 | Out | 06-05-2016 13:50 |
| 124 | In | 06-05-2016 14:00 |
| 124 | Out | 06-05-2016 20:00 |
| 124 | Out | 06-05-2016 22:00 |
Now I want to have the corresponding in and out together in one row before which I plan to number the pairs with the same number so that I can join them based on Employee ID and the pair number.
| Employee ID | In/Out | Time | Pattern Number |
| 123 | In | 06-05-2016 00:00 | 1 |
| 123 | Out | 06-05-2016 16:00 | 1 |
| 123 | In | 07-05-2016 00:05 | 2 |
| 124 | Out | 06-05-2016 13:50 | 3 |
| 124 | In | 06-05-2016 14:00 | 4 |
| 124 | Out | 06-05-2016 20:00 | 4 |
| 124 | Out | 06-05-2016 22:00 | 5 |
so that when I join it, I would get this
| Employee ID | In Flag | Time In | Pattern Number | Time Out | Out Flag |
| 123 | In | 06-05-2016 00:00 | 1 | 06-05-2016 16:00 | Out |
| 123 | In | 07-05-2016 00:05 | 2 | | |
| 124 | | | 3 | 06-05-2016 113:50 | Out |
| 124 | In | 06-05-2016 14:00 | 4 | 06-05-2016 20:00 | Out |
| 124 | | | 5 | 06-05-2016 22:00 | Out |
The logic of this I think is to increment a global variable by 1 for every different employee id and for a particular employeed id check the next row flag if its different from the current row assign the pattern number the same as current row and increment it for the row+2.
I have been trying out quite a few ways to achieve this:
1)Batch Macro
2)Multi-row formula
Also, its sorted by Employee Id and the Time.
I don't know if i'm missing something really silly but I can't get the Pattern Number to increment properly.
Anyone has a better way to solve this in Alteryx? Please let me know.
