Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Assigning a value based on column pattern

5 - Atom



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 IDIn/OutTime
123In06-05-2016 00:00
123Out06-05-2016 16:00
123In07-05-2016 00:05
124Out06-05-2016 13:50
124In06-05-2016 14:00
124Out06-05-2016 20:00
124Out06-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 IDIn/OutTimePattern Number
123In06-05-2016 00:001
123Out06-05-2016 16:001
123In07-05-2016 00:052
124Out06-05-2016 13:503
124In06-05-2016 14:004
124Out06-05-2016 20:004
124Out06-05-2016 22:005


so that when I join it, I would get this 


Employee IDIn FlagTime InPattern NumberTime OutOut Flag
123In06-05-2016 00:00106-05-2016 16:00Out
123In07-05-2016 00:052  
124  306-05-2016 113:50Out
124In06-05-2016 14:00406-05-2016 20:00Out
124  506-05-2016 22:00Out


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.


20 - Arcturus
20 - Arcturus
  1. Input data
  2. formula
  3. sort
  4. multi-row formula

You need to convert time to YYYY-MM-DD HH:MM format

Then you can sort by employee ID and time (both ascending)


Now you can use a multi-row formula and creat a new variable for PATTERN as an INT64



The formula will be constructed by:


if [in/out] == "In" then [row-1:Pattern] + 1

ELSEIF [in/out] =="Out" && [employee id] == [row-1:Employee id] then [row-1:Pattern]

Else [row-1:Pattern] + 1



I am typing the formula on my phone, so be aware that it might not be formatted correctly. 


I'm fairly certain that this will get you where you are headed. But, here is a link for instructions on the multi formula tool

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
5 - Atom

Thank you so much, Marquee!

That really helped!


Was too distracted by Macros that I almost forgot how Multi-row tool works I guess! :)

20 - Arcturus
20 - Arcturus
Easy is a good way to go.
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.