3 Replies Latest reply on Jun 10, 2016 2:59 PM by Adam.Cogswell

    Data Structure - order, join vs blend

    Mark Justice

      I am trying to create some dashboards for my first project in Tableau.

       

      For now, I would like to use tables that I created in an Access database.

       

      I currently have 2 tables - 1 that shows detailed sales results.  I will eventually need to add several more tables.   Most of the initial metrics for these dashboards will come form this 1st table.

       

      This detailed file shows daily results at the customer and product level as well as the sales person that sold the account.

       

      However, some metrics will require some type of "join" to the 2nd table which shows weekly time tracking information for each of our sales associates.  We will use this table to derive productivity metrics (think sales per day worked...).

       

      I am confused about which table to make the primary, and if I should use a join or a blend.  Each file has a date attribute and an employee number that can be used for the join

       

      Since they are both in Access, I know they can be joined, but there is not a 1 to 1 relationship at the row level between the 2 files, so I don't know if I should attempt to "blend" instead.  If I should, I don't know how to do that from a single data source.

       

      The files are large (>25MB) so I don't think I should attempt to upload.  I have tried to go through the on-line training resources, and can't seem to find anything specific to this type of question.

       

      Thanks for any help

        • 1. Re: Data Structure - order, join vs blend
          Adam.Cogswell

          Hi Mark, Joining provides more flexibility than blending, so if you can, I'd always recommend a join over a blend. And since your tables are in the same Access DB, you can use a join. There's some additional info here.

           

          As to how to join, it's harder to answer that without knowing more about what your tables look like, but it sounds like you'll want to join on both date and employee number. If you expect to have some records where a salesperson has information in the time tracking table, but didn't make any sales that day and therefore has no records in the sales table, then you'll want to make the time tracking table the primary, and use a left join. That way you'll keep all your time records, and then add any sales records that match.

           

          For background on the different types of joins and what they mean, check out this video: Join Types with Union | Tableau Software

          • 2. Re: Data Structure - order, join vs blend
            Mark Justice

            Thanks Adam.  I have tried to join in a previous version, but using the detailed results tab as the primary gave me wrong results.  When  I looked at the data below the join, it looked like it was putting a summary work time value on every detail result record.

             

            Because there is not an exact match on either file, I think I need to use left joins.

             

            For each record in the detail results file, there is NOT a corresponding record in the summary time file; and for every record in the summary time file, there is NOT a corresponding record in the detail results file.

             

            One person told me that since the files are at different aggregation levels, I should use a blend instead.

             

            I fear that I will need to replicate the data and join od different fields based on the view

             

            FYI, the first link did not work, and I could not find anything useful in the 2nd link.  I have watched all of the "connecting to data" videos.

             

            I am trying to attach the workbook and both data sources this time.  I used blends and have created 4 worksheet to show the different results I get based on which file I pick as primary and based on  a join using employee ID and Pay period.

            • 3. Re: Data Structure - order, join vs blend
              Adam.Cogswell

              Oops, looks like I had a typo in the first link. I've edited it so it should work now.

               

              I looked at your workbook, and I took a crack at building a sales / day calculation. I might not be counting sales correctly, but this should give you the idea. I actually did it two ways since it doesn't matter which data source you make primary in this case. They're both using a data blend.