Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Find and sum corresponding transactions

Bruce_V
7 - Meteor

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:

 

FromToAmount
AB100
AB300
AC200
BA-100
BA-200
BC200
CA-200
CB-150

 

Expected output:

FromToSum_Amount
AB100
BC50

 

 

Thank you in advance,

Bruce 

 

8 REPLIES 8
GavinAttard
11 - 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. 

 

 

quick way.PNG

Alteryx Everything, Leave no one behind.
Thableaus
17 - Castor
17 - Castor

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. 

 

Capturar.PNG

 

 

WF appended.


Cheers,

Bruce_V
7 - Meteor

Hi Gavin, 

 

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

Bruce_V
7 - Meteor

Hi Thableus, 

 

Will try your solution now.

Bruce_V
7 - Meteor

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
11 - Bolide

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.

 

Capture.PNG

Summarising produces the same output you were expecting.

 

Capture.PNG

 

Sam :)

carlosteixeira
15 - Aurora
15 - Aurora

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

 

 

 

Carlos A Teixeira
Bruce_V
7 - Meteor

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

 

 

Labels