community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

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:

 

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

 

Expected output:

FromToSum_Amount
AB100
BC50

 

 

Thank you in advance,

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. 

 

 

quick way.PNG

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. 

 

Capturar.PNG

 

 

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, 

 

Will try your solution now.

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.

 

Capture.PNG

Summarising produces the same output you were expecting.

 

Capture.PNG

 

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