1 Reply Latest reply on Jan 23, 2019 8:40 PM by Mahfooj Khan

    To blend or to join?

    Richard Foxworthy
    Richard Foxworthy Jan 23, 2019 7:53 PM

      I have a data source that generates regular updated flat files by date - so the internal structure and data types remain exactly the same, but there is a new and separate flat file generated with each refresh cycle, and the data within each file can vary.

       

      Data is structured in rows, each row has a location, retailer, product name, consumption amount and price fields. I have linked the two data sources using Location, Retailer, Product, Consumption fields.

       

      I need to identify and analyse data changes between the files and have been trying to achieve this by using calculated fields such as :

       

      ATTR([Total Cost])-ATTR([RE Dec 2018].[Total Cost])

      if ATTR([Total Cost]) = ATTR([RE Dec 2018].[Total Cost]) then 'same' ELSE 'different' END

       

      Tableau seems to think that the data in every field is 'different' which it is not, and also calculates the price difference between them as 'null' in every case

       

      Is this due to blending rather than joining the data sources? What is the right way to achieve this?

      • 42 Views
      • Categories: Calculations
      • Tags:
        • 1. Re: To blend or to join?
          Mahfooj Khan
          Mahfooj Khan Jan 23, 2019 8:40 PM (in response to Richard Foxworthy)

          Hi,

           

          Lets talk about first difference between joining and blending data:

          Joining your data can only be done when the data comes from the same source, for example from two sheet tabs within a single Excel file or two system tables from oracle database or MS SQL Server db. If that same information was stored in separate Excel files or in two different database servers, you would need to do a data blend in Tableau. A blend is always required if the data is stored in two separate "data sources" within Tableau. So even if your data is very closely related and exists in two separate files or databases, you will have to do a data blend if you are combining the data in Tableau.

           

          When blending data, the first data source used in your view will dictate how your worksheet view in Tableau is built. The secondary (blended) data source will be able to contribute extra information, but will not be able to change the overall structure of the view. The secondary data source's values can be aggregated and applied to the existing view after you have established a "relationship" by assigning a variable that both the primary and secondary data sources have in common.

          While using data blending there are some data blending limitations around non-additive aggregates, such as COUNTD, MEDIAN, RAWSQLAGG, LOD{} expression, table calculation etc. Follow the link for more details Troubleshoot Data Blending - Tableau

           

          When you should use data blending or joining?

          Try to avoid data blending when you can join the two data sources outside of Tableau. If not, then you must have at least one common field shared by the two data sources you want to blend together. Whenever possible, go for a join instead of blend. If you need to combine two data sources and for whatever reason cannot manage to join the data outside of Tableau, your only option is a data blend.

          As and example: (1) a data source with three columns including Category, Sub-Category and Sales, and (2) a data source with Sub-Category and detailed information about each Products sales. You could get Category sales using (1) and then blend in extra supplemental information using (2), where a relationship is built by connecting the data sources based on the Sub-Category.

           

          You can only use joining when your data comes form the same underlying source (for example, the same Excel file or Access file or any database).

           

          Let us know if this help.

           

          Mahfooj