Reconciliation.

[vivek_Hargude]

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.

[cpet13]

Hello @vivek_Hargude. I'm a little confused about your problem, why a simple join tool will not work. If you use a Join tool to join the two datasets, will that not get you what you are looking for?

[vivek_Hargude]

Hi @cpet13 there could be multiple records in the second dataset corresponding to a single record in the first, the task is to match the sum of multiple records with same /different dates in the second data with first data set

[cpet13 · Accepted solution]

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 solution]

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