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

    Record Level Comparison Between Two Files

    Jeff Bloomfield

      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 IdFile 1 IdFile 2 Id
      123451234512345
      234562345623456
      3456734567
      4567845678
      567895678956789
      678906789067890
      7890178901
      8901289012
      9012390123

       

      Thanks!

       

      Jeff