Need help for creating transaction recon tool with help of Alteryx

[scorpion2024]

Hi,

I'm new to Alteryx world and need help in creating a Reconciliation tool for monthly transactions.

1. We will receive client provided transaction report which we need to reconcile with our books and give specific comments for mismatch for : Amount, Deal name and Currency and date of transaction.

2. Challenges: Deal name from client provided report is not same in our records for eg : in client report 1 deal name is ABC capital and, in our books, it is registered as Alpha Beta capital partners. 

so, considering this deal name challenge how we reconcile rest of the elements like amount and ccy.

3. Input: a. Transaction records provided by client. 

              b. Transaction records as per bank.

Output: Data from client provided should be stacked next to bank records data according to deal name and recon column should be create for amount, currency, date of transaction and deal name (comments should be provided matching or not matching). also, data which is not matching with client records but is a part of bank records should be part of final output and can be stacked below the reconciliation data. 

Attached sample screenshot of final output. 

[Brankl]

Hi Scorpion. When you're trying to ensure all that information matches, I think your best bet is to concatenate multiple columns into one. You probably want to convert everything to uppercase letters (notice "USD" and "Usd" in your input are not identical). Feel free to delimit the results with an underscore or something. Then, once you've got two different tables each with a combined result column, you can use the join tool to see what matches and what doesn't. Partial matches are hard--how do you know if the name is spelled differently or if this is a completely different transaction for $123.45? I'd start by eliminating the full matches and then look at mismatched results from there. Sorting by dollar amount or client name can help, but there's no good way to "match up" data that may or may not have a corresponding entry. 

[paritosh96]

Sample data

[paritosh96]

Thanks for your help

[paritosh96]

...data?