Find and sum corresponding transactions

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

Dynamic Processing

Join

Accepted answers

Thableaus

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,

Solution1.yxmd

All comments

GavinAttard

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.

quick way.PNG

Thableaus

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,

Solution1.yxmd

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

Find and sum corresponding transactions.yxmd

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

corresponding transactions.yxmd

Bruce_V

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335