Assigning a value based on column pattern

[Aiswarya]

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.

[MarqueeCrew · Accepted solution]

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

[Aiswarya]

Thank you so much, Marquee!

That really helped!

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

[MarqueeCrew]

Easy is a good way to go.