Data Reconciliation

Question

Hi all,

I am very new to alteryx and needed some help reconciling some email data. The only column in the source file is "emaildata" and lists all of the email message IDs. I want to make a reconciliation that will show the number of emails per the source file, the number of emails per the Alteryx import data, and show any differences between the two. Can anyone help me out with this? Thanks in advance!

RobertOdera · Answer

Hi, @Dougyd

As per @ChrisTX  suggestion, it is always best to share a sample.

In the meantime:

1. From your original source file: Use the Summarize Tool under Transform --> Group by email message IDs, Count of email messages as Source_Count

2. From the Alteryx Import file: Use the Summarize Tool under Transform --> Group by email message IDs, Count of email messages as Alteryx_Count

3. Use the Join tool under Join --> Inner Join on Source email message ID == Alteryx email message ID

4. From your J node (those that joined), use the Formula tool under Preparation where the new column Matched is given by IF Source_Count = Alteryx Count THEN "Yes" ELSE "No" ENDIF. Add another Column Diff_Count, which is given by ABS(Source_Count - Alteryx_Count) --> this will give you the count shortage

5. From the L and R nodes will be those email message IDs that did not join --> therefore, these are in one source but not the other

Cheers.

Please provide a sample for your use case so the community can dial you in further 👍.

Please mark this as an acceptable solution if it works for you.

ChrisTX · Answer

You could use a Count tool to simply count the total number of records in both sources, then a Join tool, Join by record position, to ensure the two values are the same.

But I'm guessing your actual requirements may be a little more complicated.

Posting sample input data and desired output would help.

Chris