Matching Transactions to a Single Total

Question

I have a 2 sets of data that have individual transactions on one of the sets, and Sum Totals in the other.  I'm looking for a way to match up the transactions to the correct total

This feels like an iterative macro, but i'm having trouble conceptualizing the correct way to do it:

Set 1:

TransactionRecordID616.661112.142392.63356.1141115336.78611227336.488112.49

Set 2:

TotalRecordID2759.911

the output should be that Transactions 1,2,3,7,8 sum up to match the Total...

kunalkalani · Answer

Hey @esmalley and @JSNelson08 ,

Thank you for this post, it is helping me with a current workflow I am trying to build to perform some reconciliation work. Is there any chance you may be able to share your nested macros that you used? It would be a great help to structure my approach on the work I am trying to do!

Many thanks!

esmalley · Answer

Hi all,

Thanks to JSNelson08 for submitting this - he was helping me with this project internally.

I ended up resolving this with nested macros.  The outer macro is a batch macro that does the following, grouping by a field called "contract type" to reduce the data volumes:

1) Add record IDs to the set of records

2) Check to see whether the sum of all prices x quantities for this batch match.  If so, don't pass anything to the inner macro, since we already know the total batch is matched.

3) Pass the unmatched items to the inner macro twice, since the macro takes one set of records as a constant and iterates on the other.

The inner macro is an iterative macro that first checks to see if any of its Right input match off against the Left, and if not, does a Cartesian join for its Right input to itself (i.e. creating a matrix of [price x quantity] xx [price x quantity] data, summing [price x quantity] and concatenating the record IDs with a comma delimiter).  It then iterates on the joined output.

I had to put a couple of checks into the iterative macro to try to further reduce data volumes however I could.  The big one was to take any [price x quantity] combinations that get matched out of the data set entirely, so that the iterative process wouldn't continue to balloon out on the records that already found their match.

Then the macros spit out the matched sets of record IDs and their prices x quantities, as well as anything that remains unmatched.

This was definitely a more difficult project than I anticipated when I was describing it to my office, and huge thanks to JSNelson08 for his help!  Obviously this workflow can really start to chug if the data set gets too large, so I set the maximum iterations down to 3.  It doesn't always match everything off as a result, but it still does a pretty solid job with most situations. The only ones that it can't match (assuming it SHOULD, i.e. our data set is valid and should be matchable) are sets where there are a large number of records coming through within a single "contract type" batch.  I did a back of the envelope calculation assuming each record in the iterative macro (price x quantity, record id) was only 10 bytes, and for a 20-item set, the full Cartesian join is ~2.6MB.  A 30 or 50-item batch will be too much for Alteryx to handle in a reasonable amount of time, so I just restricted the number of iterations and we deal with the resulting occasional failures to match as a manual procedure on the back end.  This does save us a lot of time even with that limitation!

acastelazo · Answer

Interesting problem. This could be one of the weekly challenges!

It's true that the number of permutations could get big very fast. Fortunately, what you are really looking for is the number of combinations (a much smaller number). This is defined by: n!/[r!(n-r)!].

The thing you have to determine beforehand is the maximum number of transactions that can add up to your totals. Here's my attempt with the sample data you provided:

matching_transactions.yxmd