1 Reply Latest reply on Feb 14, 2011 7:49 PM by Richard Leeke

    BLEND Data with Outer Join

    Robert Lingard

      I'm working on a neat trick for validating data sets that we generate for Tableau.  We want to make sure the data ties out to the master corporate data before releasing it in Tableau as a data quality check, but it's a very time consuming and potentially endless exercise. 

       

      Then we thought of copying and pasting equal views formatted the same but from the two different data sources that we need to compare, right into Tableau workbooks with Edit/Paste Data.

       

      Next we go to the workbook that has the control data, from our corporate database, and create a simple calculated item that sums bookings from the corporate data and then subtracts them from the bookings that we need to validate before using in Tableau.  The formula is:

       

      Name:  DIFF

       

      SUM([Corporate_Bookings])-SUM(Clipboard100007456392.Tableau_Bookings)]

       

      You can build the formula in Release 6 by just clicking on the desired Clipboard in  Edit Calculated Item.

       

      Next just drag DIFF into the work area and all your values should be zero!!

       

      If not you've got a difference to assess and possibly investigate between the corporate data and the test data.

       

      You can even click on the test data Clipboard and drag in the test data bookings column for easy comparison.

       

      The only gotcha in this scenario is that Tableau appears to be performing a left join on the primary dataset, in this case the Corporate data.  If there's a dimension value in the test data that isn't in the Corporate data any bookings associated with that dimension value will be dropped.

       

      Does anyone know how to make Tableau do an outer join when BLENDING data like this?

       

      Thanks,

      Bob