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

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.

Differences but Matched 2.xlsx

Spot Differences Between 2 Different Systems.yxmd

Common Use Cases

Accepted answers

T_Willins

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

T_Willins

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.

All comments

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/9414

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-Calling-out-Differences/m-p/63727#M23078

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-servers/td-p/517599

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.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335