Need help in building a Reconciliation between two data sets

[mohit9garg]

Dear Experts,

I need your help in building a reconciliation between two data sets. I am attaching an excel example here but needless to say that actual data set is very large.

Thanks in advance.

Mohit

[DavidP]

Would it be something like this that you're looking for?

[ponraj]

Here is the sample workflow.  Hope this is helpful. .

[mohit9garg]

Apologies, this solution didn't work as my actual data is in V_String & Double so Join Multiple is not working.

Apologies, I should have mentioned this earlier.

[mohit9garg]

@ponraj  @DavidP  @grossal ...I am attaching another example of rec. Actual data is similar to this one. Please help.

[AbhilashR]

Hi @mohit9garg, does the attached solution get you the output you are looking for? 

Let us know.

[mohit9garg]

@AbhilashR

Thanks. But while joining the 2 data sets, I am getting an error-

Error: Join (9): String fields can only be joined to other string fields.

[DavidP]

I amended my original suggestion for your new data. I used Symbol ID to join the 2 data sets, but then noticed that Symbol ID is not unique in data set 2, and neither is Stock Symbol, as shown below. I appreciate that this is only mock data, but it's an important consideration when joining 2 data sets. If those fields are unique, then you can use either to join the data sets together, but if not, then you have to find a unique combination of fields, for instance in the example below, a combination of Stock Symbol and Symbol ID would give you a unique matching criteria, so that is what we'll use.

With the Select tools before the join tool, you can rename the fields by adding Data 1 and Data 2 respectively. This is also the place where you would change data types of fields if required, so that Symbol ID and Stock Symbol have the same data type in both sets for example. If some of your data fields are not numeric data types, you'd change them here too, otherwise you won't be able to subtract them from each other later on. So use these Select tools to get your data in good shape before joining it.

We use both the left (L) and right (R) output of the join, together with the join (J) output, because you want all the data from both data sets in your final output, not just the fields that have joined.

The Data Cleanse Tool turns all the nulls to zeros and then the formula tool simply subtracts the data 1 and data 2 fields from each other.

Finally, the select tool is used to sort the order of your columns and the sort tool orders the rows to be the same as they were before the join tool.

Hopefully, the attached workflow does what you asked for and I hope my explanation makes things a little clearer, but if not, do try to identify and solve the problem yourself first as it's the best way to learn, and if you get stuck we'll be here to help.

Happy Alteryxing!

[grossal · Accepted solution]

Hi @mohit9garg,

I am sorry for the late reply, I did not receive a notification for your tag 😞

I wanted to give this a clean attempt and did not look what the other already made - so maybe it's already solved, but here is my approach:

Let me try to explain what I do:

- First I transpose all the data column into rows to make it possible to dynamically calculate the difference

- I add a new column with  + 1 / 2 to separate them later on (Formula)

- I Join everything together and Union it afterwards to get all data combined 

- The Select tool changes the type of the value columns to double

- Data Cleansing to convert nulls to zero

- Formula to calculate the difference

- Next I separate the data with 3 Select tools into Name Value pairs. Name Value from Left Dataset, Name Value from Right Dataset and Name Value "Difference" form both. I rename all to just "Name Value" here

- Union everything together

- Filter Nulls again

- I decided I want to keep the column order. The first input has a wireless connection to the select tool in the second path. I remove all key column and use the Field Info tool to get the column names in rows. The Multi-Row-Formula adds a counter column and the Formula Tool combines both into a "1 Column name" format to preserve the order.

- Find and Replace to bring everything together

- Now I am finally able to Cross-Tab everything based on the ordered names

- Multi-Field-Formula to convert nulls to zero

- Dynamic Rename to remove the "order numbers" in front of the columns and also to change the underlines to normal whitespaces.

I'd guess that some of the other solutions are probably more easy. I am usually overcomplicating stuff because I try to dynamic and flexible as possible. This solution should also be able to handle future extra columns in the Input Data and applying the Difference Formula to them and bringing all into shape.

Workflow is attached. Let me know what you think and sorry again for the late response 😞

Best

Alex

[AbhilashR]

Hi @mohit9garg, include a Select tool between your source file and the join tool and make sure the datatypes for the columns you are joining on are same. Doing so will address the datatype mismatch issue. Others on this thread have done so in their solution. You could look at their work to get a sense of what I am referring to.