alteryx Community

Alteryx Designer Desktop Discussions

SOLVED

Assigning a value based on column pattern

5 - Atom

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.

3 REPLIES 3
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

EndIf

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

http://help.alteryx.com/10.6/index.htm#MultiRowFormula.htm?Highlight=Multi%20formula

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
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
Easy is a good way to go.
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.