Finding values which is coming net to zero

Question

I have multiple transactions from salary and I have to figure out the pair which is sum is not equal to zero

From the example below:  Salary Id;123 => 7000+(-7000) = zero-> Eliminate (could be multiple transaction with same value pairs)

Salary Id 123 => 7200+7000 + 2000+8800 = 25000 (which is equal to salary amount) -> Keep it

Same goes for                   Salary Id 789 => 789 => -2500 + (-2500 )= -5000 (which is equal to salary amount) -> keep it

Note: The Idea is to eliminate the pair which is coming as zero (which is opposite to each other and should be pair)

salary_IdTransactionsalary123700025000123200025000123720025000123700025000123-700025000123700025000123-7000250001238000250004563800880045650008800789-2500-5000789-2500-5000

Output:

salary_idTransactionsalary1237200250001237000250001232000250001238800250004563800880045650008800789-2500-5000789-2500-5000

jdunkerley79 · Accepted Answer

I suggest the following:

Create a RecordID grouped by salary_id and transaction amount

Create a Key field of salary_id and abs transaction amount

Join the positive and negative transaction and eliminate matched ones (same key and RecordID)

Keep just the unjoined records

Should produce the set of results you want

Sample attached