Data360 Analyze

Hello,
I have a data set where I have positive expense amounts and negative expense amounts.  I'd like to find a way to sort by the source ID and then look for a possible match.  If found, mark the matching negative/positive so I can than research and adjust, if needed. 

Can D360 be used to sort the data by the source and then look for the "equal" negative/positive and then mark those two transactions so I can look and confirm the adjustment?

The attachment is a sample of the data I'm looking to adjust.

Attachment(s)

negative positive D360 sample data.xlsx   9 KB 1 version

I'm sure there will be several ways to reach the goal, but this is the one that comes to my mind.

1. Create a two new colums:
   1. one with the row id (node.execCount): AV_Expensive_Amount
   2. one with the absolute value of Expense_Amount (without sign): RowID_1
2. join the data with themselves by Source_ID and AV_Expensive_Amount 
   1. Matches: all in in1 + RowID of in2 (RowID_2) + Expensive_Amount in in2
3. filter the matches pin for:
   1. take off the rows where RowID_1 and RowID_2 have the same value
   2. (optional) take off the rows that have the same value in Expensive_Amount form in1 and in2 (both are positive or negative, I am not sure if you want to get these rows)
4. Mark each row as unique value (RowID_3)
5. use Join nodes twice for join the data you had in the step 2 :
   1. with RowID_1, adding RowID_3
   2. with RowID_2, adding RowID_3
6. Now, you have the original file where all rows with the same value in RowID_3 are the rows you want related.

Hope can help you.

Ragards

I used a similar approach to the solution described by 'Soporte LAE'.

However, as the data type of the expense amounts is double (i.e. floating point numbers) this may cause issues with matching some amounts due to the minor rounding errors that are inherent in floating point numbers. Best practice would be to convert the monetary amount to cents with an integer data type and use that for the matching. I matched on the cents amount and the inverse cents amount - meaning that only one join was required. The joined data was then sorted by the source and the absolute cents amount. See the attached zip file containing the .lna file

Attachment(s)

Match_Transactions_By_Source_368 - 17 Aug 2023.zip   4 KB 1 version

Adrian, this actually worked very well.  Thanks for the help and the .INA file.