1 Reply Latest reply on May 23, 2012 10:29 AM by Jonathan Drummey

    How to blend data without having certain fields calculate more than once

    Connie Walker

      This workbook contains three data source:

      MainData

      Payments

      PostDatePayment

       

      All three are to be linked by DBACCT and/FACS (depending on which table you are looking at)

       

      Is there any way I can blend all three source without having data calculated more than 1.

      For Example:

      When I blend the sources together the “$$ placed” goes from 16,441,557.45 Jan 2012 to 17,604,168.46.  It seems that the field are getting calculated

      as many times as there are rows for the DBACCT/FACS appears since Payments may have more than 1 row for DBACCT/FACS

       

      I am attaching two copies of my project - one with the calculations computing  correctly but tables not blended  (Tableau_Forum_BlendingData_Tables_Not_Linked) and one where the tables are linked but the calculations are computing  incorrectly (Tableau_Forum_BlendingData_TablesLinked.twbx)

       

      The overall end result I would like is to have all three tabs (Batch Analyzer / FUture Payments / Payments) to have the data in 1 tab rather than 3 different tabs

       

      any suggestions are would be a great help

       

       

       

      Thanks

        • 1. Re: How to blend data without having certain fields calculate more than once
          Jonathan Drummey

          Hi Connie,

           

          I took a look at this and at least at first glance it's not an issue with Tableau data blending, rather the data source as you have defined it. Due to the way you have generated the extract, I am unable to see the query that creates the MainData data source. Your description of what is happening (multiple payments per DBACCT/FACS) makes sense to me as a likely cause of the problem. There are two options:

           

          1. In the query that you use to create the MainData data source, instead of joining to individual payments join to a subquery that pre-aggregates the payments.

           

          2. Use Tableau's data blending functionality. In order for data blending to work, the linked fields need to be in the overall level of detail (Rows, Columns, Marks card shelves) in the view. So you'd need DBACCT/FACS in the view, and when you are at that level of detail then Tableau will be performing the SUM, etc. calculations for each DBACCT/FACS, which is too detailed for what you want. To deal with that, you need to jump up an aggregation level to use table calculations.

           

          In walking through your data, I didn't see any calcs that would be too hard to do using option #2, however it would be time consuming. If you can change the query, I'd suggest option #1.

           

          Cheers,

           

          Jonathan

           

           

           

           

           

           

           

           

          When I drag Number of Records into a new view in the Not Linked file using the VerizonCAREReportData)20120506+ data source, there are 78,934 records. When I do that in the Linked file using the MainData data source, there are 82,094 records. You can also see this in the Linked file if you drag number of records into the Batch Analyzer view, there Number of Records does not equal CNTD(DBACCT), so some records are getting duplicated.

           

           

          Jonathan