Find and sum corresponding transactions

[Bruce_V]

Hi all, 

Background

I've tried googling and seraching within the Alteryx Community but could not find anything. I hope I can make my problem clear enough that you understand and can help. 

I have a dataset with transactions, important fields are [From] [To] and [Amount]. In certain cases, the combination [From] [To] is available more than once but in different order (see data example below). 

Question

How can I find the corresponding transaction as I want to sum these to see if the total amount corresponds. 

Example data:

Input:

From	To	Amount
A	B	100
A	B	300
A	C	200
B	A	-100
B	A	-200
B	C	200
C	A	-200
C	B	-150

Expected output:

From	To	Sum_Amount
A	B	100
B	C	50

Thank you in advance,

Bruce 

[GavinAttard]

Hi

A real quick way if i understood the task at hand correctly is:

if the reverse transaction (B to A) is always negative. Filter for all negative amounts. Reverse the From and To, re-union then sum. 

[Thableaus · Accepted solution]

Hi @Bruce_V

Technique I used here is to assign a Number to each unique Letter (from fields FROM and TO), and them sum them up to group by each pair.

EDIT: I also multiplied them, as I realized that a Pair sum can be equivalent. 

So I have a new pair, which is the result of their multiplication. 

WF appended.

Cheers,

[Bruce_V]

Hi Gavin, 

Thank you for your reply. However, in certain cases reverse transaction is not negative but the 'regular' transaction is negative. 

[Bruce_V]

Hi Thableus, 

Will try your solution now.

[Bruce_V]

Unfortunately it doesn't work yet but it is a great baseline. Will continue from here. If you have any great brainwaves do let me know. 

The reason that it does not work is that the matching of the pairs is a little off. 

[SamDesk]

Hello @Bruce_V,

I think I have a flow that meets the needs you set out in your first post. It works by first assigning a unique identifier to each row (Record ID), then creating a mirror version of the data and unioning back to itself. Effectively it doubles the data with the From and To fields swapped. Then it sorts on the From field and restricts to one record per Record ID to always show the highest alphabetical 'company' in the From field.

Summarising produces the same output you were expecting.

Sam :)

[carlosteixeira]

Hi @Bruce_V 

I thought of something a little simpler than my friends. A little more work for having to make several combinations if you have many variables. I holpe it´s help

[Bruce_V]

Thank you @Thableaus - your chain of thought / workflow does indeed work.