cancel
Showing results for
Did you mean:

Alteryx designer Discussions

Find and sum corresponding transactions

Meteoroid

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

Bruce

Highlighted
Bolide

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.

Alteryx Everything, Leave no one behind.
Alteryx Certified Partner

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,

Meteoroid

Hi Gavin,

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

Meteoroid

Hi Thableus,

Meteoroid

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.

Comet

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 :)

Alteryx Certified Partner

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

Labels