Creating a new column to tag related records

[vham]

I previously had a solution for this in Excel/VBA, but in trying to recreate my work in Alteryx I have been unable to replicate a certain feature.

I am working with a ledger of transactions and for those unfamiliar, there are some transactions known as accruals. Accruals in this situation are expenses that have occured, but have not yet been recorded on the ledger. Anyway, accruals reverse automatically on the first day of every month in the form of an offsetting record. Our ledger does not identify Accruals or their reversals in the database so logic must be built in order to idenify them. Here is a simplified sample of our ledger.

Trans No.	Posting Date	Acct Unit	Description	Amount	AutoRev
1	9/15/2018	1234	Tuition Reimbursement	15000	Y
2	9/17/2018	5678	Desk Lamps	4000	N
3	9/20/2018	1234	Catering Services	2350	N
4	9/20/2018	1234	Training Class	1500	N
5	9/27/2018	5678	Employee Rewards	800	Y
6	9/28/2018	1234	Employee Rewards	400	N
7	10/1/2018	1234	Tuition Reimbursement	-15000	N
8	10/1/2018	1234	Chairs Refund	-800	N
9	10/1/2018	5678	Employee Rewards	-800	N

The accruals themselves are easy to identify. The AutoRev column means auto-reverse and any time an accrual is made, it is flagged with a "Y" in that column. The reversal is trickier to identify. You need to search the next month for a record with matching values and an inverse transaction amount. In the above table, Transaction 1's reversal is Transaction 7 and Transaction 5's is Transaction 9. I want to create something that can tag these accruals and reversals with the end result looking something like this:

Trans No.	Posting Date	Acct Unit	Description	Amount	AutoRev	Accrual/Reversal
1	9/15/2018	1234	Tuition Reimbursement	15000	Y	Accrual
2	9/17/2018	5678	Desk Lamps	4000	N
3	9/20/2018	1234	Catering Services	2350	N
4	9/20/2018	1234	Training Class	1500	N
5	9/27/2018	5678	Employee Rewards	800	Y	Accrual
6	9/28/2018	1234	Employee Rewards	400	N
7	10/1/2018	1234	Tuition Reimbursement	-15000	N	Reversal
8	10/1/2018	1234	Chairs Refund	-800	N
9	10/1/2018	5678	Employee Rewards	-800	N	Reversal

I have been looking online for solutions, but I couldn't find anything that was related.

The macro I created in excel looped through all of the records and if it found a records with AutoRev = "Y" then it looped through all of the records again to find a record that had matching values in all of the columns and an inverse value in the amount column, and it would tag the original record as "Accrual" and the found matching record as "Reversal". Any ideas as to how to tackle this would be much appreciated! (Very new to Alteryx fyi)

[BenMoss · Accepted solution]

Although this seems like a complex problem there is a relatively straight forward solution.

You can solve your problem with a filter, join and union (plus a few more steps!)

I've built out your solutions, take a look, and if you want me to explain any steps then let me know!

Ben

[DavidP]

Here's how I would do it.

[DavidP]

Sorry Ben, didn't realise you posted a solution already.

[vham]

Thanks for the quick reply, your solution helped as well!

[vham]

Thank you so much, that helped me out tremendously!