Reconciliation.

Question

Hello everyone,
I am trying to create workflow to reconcile two sets of data. Having one to many relationship where one record in the first data set corresponds to multiple records in the second data set.
Unfortunately there is now unique data records in both the data sets that would help in reconciling . Hence I have created a Mapping data which will help in identifying the correspondence records between both data sets.

I tried to match these using the Summarize/Transpose tool with multiple join and union tools, Could anyone tell how can this be build in Alteryx.

Recon.xlsx

cpet13 · Accepted Answer

You could use a summarize tool on the second dataset to summarize the data into one record, then join to the first dataset. Would that work?

bclau00 · Accepted Answer

Reconciliation is all I do.  From the looks of your data, you have a many to many and have manually linked them to a key as well as created a compound key in both.  Can you get the date in your first set of data that links to the second?  If not, then grouping both by name and currency looks like it would be sufficient... and then UNION them before summarizing.  Then add a variance column with the formula tool.  If you want to get real fancy, push anything that doesn't link to an exception file to be used as input in the following reconciliation.

Name | Currency | Data1Amount | Data2Amount | Variance
Bus1  | AUD         | $1                   | -$1                  | $0

Bus1  | CAD         | $1                   | -$1                  | $0

Bus2  | USD         | $1                   | -$1                  | $0