2 Replies Latest reply on Dec 16, 2016 11:03 PM by Eric Yun

    Data joining issues

    Eric Yun

      Hi,

      I've been struggling with building a report in Tableau,  Any your input or your advice will be much appreciated.

       

      "Store DB" is a primary data and other data "Tue", "Wed" and "Thur",,,, sales tables need to be joined as 1:N on every day.

      * Could you please have a look at attached (File called 'Data Sources')?

       

       

       

      In order achieve cumulative way with given tables (pls refer to attached)  as below

       

      Store            Tue_Transactions     Wed_Transations      Thur _Transactions         .......

      A                    31                              35                              92                                  .......

      B                     50                             44                              66                                 ...

       

      What type of Join type and which Join fields I should use it?

       

      When joining tables in a data source, Some 'Store's displays incorrect numbers for "Total Transactions", so I used the formula of "SUM([Total Transactions]) / COUNT ([Store Id1])" to resolve the issue but some of stores display the wrong figure.

      http://onlinehelp.tableau.com/current/pro/desktop/en-us/help.htm#joining_tables.html#troubleshoot?lang=en-us

       

      Please refer to attached Test1.twbx

       

      C1.JPG

       

       

      There are two different types in "Service Type" column, one is takeout and other is delivery,  and some of stores have two all types on the daily sales, some of them are not so I can not use this "Service Type" as the secondary key to link the tables.

       

      Can you pleases have a look at these attached and advise me what I went wrong?

       

      Your support is much appreciated.

       

       

      Regards,

       

      Eric

        • 1. Re: Data joining issues
          Jonathan Drummey

          Hi Eric,

           

          You asked several questions, I've attempted to number them below:

           

          1) Data setup. Since the daily sheets are essentially the same the more appropriate approach for Tableau would be to union those sheets and then join that to the other data. Also since a store may not have any transactions a left join is preferable. Note that as of Tableau 10.1 unions are only supported for certain data sources (excel, text files, Google Sheets are three that I know of). Tableau 10.2 (currently in beta) is supposed to add in-database unions.

           

          Here's the data source I set up in the attached workbook:

          Screen Shot 2016-12-16 at 10.17.36 PM.png

           

          2) Join key. Since the store data set has stores and the daily data has more details, and therefore a one to many relationship, the join is just on storeid/storenumber.

           

          3) Some stores displaying incorrect totals. What exactly is incorrect? Also, check out the transactions by store worksheet in the attached, the change to the join may have corrected the problem.

           

          4) Service Type as possible key. I think this was a possible confusion because of the way you'd set up the original data source as a series of inner/left joins, this is no longer necessary with the union & join scenario.

           

          Jonathan

          • 2. Re: Data joining issues
            Eric Yun

            OMG, You are the champion, Jonathan!

            All good now!

             

            So using Union & Join eliminate duplicate values.

             

            Much appreciate your help, Enjoy your weekend.

             

            Eric