2 Replies Latest reply on Jul 10, 2013 10:40 AM by Jeff Bloomfield

# Record Level Comparison Between Two Files

Hi all, I'm trying to use Tableau to create some QC comparisons between two sequential monthly files and am having trouble figuring out how best to approach it.  I've got two files that each have a unique personal identifier, and using that field I want to be able to compare different fields and find out where the data has changed.  The data itself is in an Excel Spreadsheet, and each month has its own tab.

I started out by creating a unique list of all the Unique Id's, which I loaded into its own Excel tab.  I then have File 1 & 2 records on different tabs within the same Excel document, and then load them up and create left joins so that I grab all Unique Id's and any corresponding matches in the other two files.

The only comparison calculation that I am having a problem with right now is trying to figure out how many Unique Id's exist in one file but not the other.  For instance, in the example below, I would like to create a measure called 'Unique To File 1' which would have a count of 2, while 'Unique To File 2' which would have a count of 3.

I'm sure that there is an easy solution here, but I've tried so many different combinations that I've probably started duplicating stuff I've already tried, so if anyone has any suggestions I would greatly appreciate it.

 Unique Id File 1 Id File 2 Id 12345 12345 12345 23456 23456 23456 34567 34567 45678 45678 56789 56789 56789 67890 67890 67890 78901 78901 89012 89012 90123 90123

Thanks!

Jeff

• ###### 1. Re: Record Level Comparison Between Two Files

Try this:

Unique to File 1 = SUM(IF NOT ISNULL([File 1 Id] AND ISNULL([File 2 Id]) THEN 1 END)

Unique to File 2 = SUM(IF NOT ISNULL([File 2 Id] AND ISNULL([File 1 Id]) THEN 1 END)

Jonathan

• ###### 2. Re: Record Level Comparison Between Two Files

That did it - thanks for your help!