I had a great time at the meeting. I wanted to share what I've done for file compares --
I have an R tool with this code:
df1 <- read.Alteryx("#1", mode="data.frame")
df2 <- read.Alteryx("#2", mode="data.frame")
library(dplyr)
df1_only <- anti_join(df1, df2)
df2_only <- anti_join(df2, df1)
write.Alteryx(df1_only, 1)
write.Alteryx(df2_only, 2)The stuff that is only in the first dataset is output to "1", and the stuff that is only in the second dataset is output to "2". Now, this only compares on columns that are in common. So if you have a field called "Begin Date" and you try to compare it to "Begin Date (mm/dd/yyyy)", it will ignore those columns altogether. If you want to compare them, you can rename the appropriate columns with a select. This is the equivalent of a Join transformation where you map every single column to map between the datasets. So if you happen to have a one-to-many, I wouldn't catch that.
I haven't yet installed the CREW macros. One thing that I thought was cool was how you could tell which columns were different. One way I accomplish this is:
From R tool, select "1", then go to a Select Records transformation, select row 1 for example.
From R tool, select "2" into a Join. Then unselect Unknown, and map the "Select Records" as the right. Join on your primary key.
Union the output from the Select Record and the inner Join. This gives you at least one record to compare between the two systems you are comparing against.
(If necessary, you could select "Set a Specific Order Output" in the Union so that you know which record is which in the union.)
One caveat - when data goes through an R tool, spaces and special characters are converted to periods. So, "Begin Date" becomes "Begin.Date".
I attached a template.
Best wishes,
