Compare 2 Spreadsheets to Spot Differences - Stuck, Please Help!

[jmmart08]

Hello Everyone

I am completely stuck with a workflow I have built out.  What I am doing is pulling outputs from 2 different internal systems and attempting to reconcile them.  I just want to spot any differences in any of the values for certain criteria.  I have to do a lot of filtering and formatting as the 2 systems name values that mean the same thing very differently.  In my workflow, I have noticed that even when certain records mismatch, I still come up with a "No Difference" output with the formula I have. 

I have attached my Alteryx WF as well as my output spreadsheet where specific record ID's clearly do not match, yet my output shows as "No Difference" in my last column.  I cannot figure out how to avoid this.  I have highlighted those records on my excel sheet that I have the issue with.  I purposely flipped an entry between the systems to try to get the result to change.  When you look at the sheet, what happens is the person that inputs frequently enters an item in 1 system as 403A but as 403B in the other system.  When this happens, the quantity and net amount columns are off.  I see they are off, but I still get the no difference output.  Any suggestions would be GREATLY appreciated as I am completely stuck here.

[T_Willins]

Hi @jmmart08,

Your second formula in Formula Tool (8) is overriding the first formula.  The rows that meet the criteria of "Difference" in the first formula are being overridden to "No Difference" as they do not meet the parsing of the second formula.  I believe you can correct this by changing the second formula to If [Difference] = "Difference" THEN [Difference] ELSEIF DateTimeParse([Sheet1_Value],"%Y/%m/%d") = DateTimeParse([Sheet2_Value],"%Y-%m-%d") THEN 'No Difference' ELSE 'Difference' ENDIF

[ChrisTX]

These related posts may help with your overall workflow:

Community > Designer > Browse Knowledge > How To: Compare Data from Two Data sets

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/How-To-Compare-Data-from-Two-Data-sets/ta-p/...

Comparing Data from Two Sets of Data, Calling out Differences

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Comparing-Data-from-Two-Sets-of-Data-C...

Compare 2 Data Sets

https://community.alteryx.com/t5/Engine-Works-Blog/Compare-2-Data-Sets/ba-p/88853

2018 Excellence Awards Entry

Community > Getting Started > Alteryx Use Cases > Data Check Between Two Sources

https://community.alteryx.com/t5/Alteryx-Use-Cases/Data-Check-Between-Two-Sources/ta-p/270082

Comparing data between two different servers

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Comparing-data-between-two-different-s...

[jmmart08]

Hi @T_Willins ,

Thank you very much for your response.  Apologies, I am not sure which field you are referring to with [Difference].  If you could please try to explain that would be great.  Again, very much appreciate your assistance.

[T_Willins]

Hi @jmmart08,

[Difference] is how the field named Difference is identified in the formula.  Generally in formulas quotes are used to identify text (i.e. - "text") and square brackets are used to identify field names (i.e. - [Difference])

[jmmart08]

Hi @T_Willins ,

Understood and thanks for the explanation.  What I am confusing myself with I guess is I am not sure which field in my example that I would reference in my second formula.

I have tweaked my workflow just so some of the verbiage is a little different, but my first formula is

IF [TW_Value]=[XIP_Value] THEN "Matched"
ELSE "Difference" ENDIF

My second formula is,  If DateTimeParse([TW_Value],"%Y/%m/%d") = DateTimeParse([XIP_Value],"%Y-%m-%d") THEN 'Matched'
ELSE 'Difference' ENDIF.  In the example you provided me, you stated I could add some information in front of my second formula but again I am just a bit confused as to which field in my example that I reference.  Any help would be very much appreciated.  thanks.

[T_Willins · Accepted solution]

Hi @jmmart08,

Your first formula creates the field named Difference.  Your second field updates the new field named Difference.  The formula below (for the second formula in the Formula tool) looks first to see if the field named Difference has a value of "Difference" and only looks to update it if it does not have a value of "Difference".  That way you are not overriding the results of the first formula with the results of the second.  You should be able to paste the formula below in your Formula tool (2nd formula).

If [Difference] = "Difference" THEN [Difference] ELSEIF DateTimeParse([Sheet1_Value],"%Y/%m/%d") = DateTimeParse([Sheet2_Value],"%Y-%m-%d") THEN 'No Difference' ELSE 'Difference' ENDIF

[jmmart08]

Hi @T_Willins ,

Thank you now I understand.  Appreciate that.  What seemed to happen now with this is I'm still coming up with "Difference" on some records due to the date format difference, and now for some reason I have several "difference" items for my NET column because they are off by .01.  I didn't seem to have this issue before.  I copied your exact formula as shown below.

If [Difference] = "Difference" THEN [Difference] ELSEIF DateTimeParse([Sheet1_Value],"%Y/%m/%d") = DateTimeParse([Sheet2_Value],"%Y-%m-%d") THEN 'No Difference' ELSE 'Difference' ENDIF

And my first formula is as shown below.

IF [Sheet1_Value]=[Sheet2_Value]THEN "No Difference"
ELSE "Difference" ENDIF

[T_Willins · Accepted solution]

Hi @jmmart08,

This was likely being masked in your original setup by the second formula overriding the first formula's results. Alteryx will compare exact values unless you tell it differently.  You can add a Round function to your first formula if you only want to see differences at or over a certain amount.  For example, if you wanted to compare when rounded to the nearest whole number, you could change your formula to: 

IF Round([Sheet1_Value],1) = Round([Sheet2_Value],1) THEN "No Difference" ELSE "Difference" ENDIF

This will eliminate 100.00 and 100.01 from creating a result of "Difference", but would still show "Difference" for 100.00 and 100.50.  By doing this as part of the formula it maintains your original data, but allows you to adjust the sensitivity of the comparison to less than exactly matching.

[jmmart08]

Hi @T_Willins ,

This is perfect!  Agreed that this was all being masked because of my 2nd formula overriding the 1st.  Didn't understand that at first but now it makes complete sense!  Also, instead of creating a formula for the Date format I simply used the Date Tool and changed 1 of the systems' format to match the other so I just eliminated the date stuff altogether.  With this rounding, everything should now be perfect!  Thank you VERY much for all of your help on this.