3 Replies Latest reply on Aug 4, 2016 11:26 PM by San M

    Excel Join's in Tableau


      Hello Everyone,


      I was wondering if someone can help me explain this concept. I was searching through the forums and still cannot really figure out my issue. I'm currently using Tableau 7 still.


      Scenario #1

      I have 2 seperate Excel files. File #1 = "master" file and File #2 = "child" file. Not all records in file #2 will exist in file #1


      I go into Data > Excel File #1 > Edit Tables > Add Tables > Select Excel File #2 > Create a LEFT join between the tables


      When I do this, I'm expecting to see only 100 rows since file #1 only has 100 rows. But after the join, I see 120 rows.


      Why do I suddenly have more rows if I do a LEFT JOIN, I thought I would get all the rows in my left table (100 rows) and any matching rows in the second table, like how it is in SQL.


      Scenario #2

      I combine the two seperate Excel files into one Excel file, split by multiple sheets.


      I go to Data > Edit Relationships > Set Primary data source = File #1 > Set Secondary data source = File #2 > Do a custom Link between the 2 workshets


      The results are then correct, I only see 100 records and any values from file #1 that do not have a corresponding match in file #2 is NULL.


      Why did it work this way?


      Appreciate if anyone can point me in the right direction or offer some type of explanation.


      Still new and trying to understand the data blending concept.


      Thanks so much!

        • 1. Re: Excel Join's in Tableau
          Frances Holland

          Scenario #1 is probably working correctly.  This might happen if the field(s) you are joining on occur more than once in the second file.


          For example, say you have this data in file 1:

          1     Green

          2     Blue


          And in file 2 you have:

          1     Yellow

          1     Purple

          2     Red


          If you left join on the number field, you'll get three lines back, even though there are only two in file 1:

          1     Green     Yellow

          1     Green     Purple

          2     Blue     Red


          Does that make sense?  The data blending (scenario 2) works totally differently, in that it can only return one result from file 2 regardless of how many lines there are.  It forces a situation that doesn't reflect the truth in the underlying data, and you'll see this anywhere it returns * instead of the value because it finds more than one match.

          • 2. Re: Excel Join's in Tableau

            Hi Frances,


            Thanks so much! That was exactly the issue I was having.


            I had thought I cleaned up my file #2, but when I checked again I found a record that was occuring more than once. Which was causing the extra records to appear when I was doing a left outer join in my Scenario #1.


            Once I gotten that cleaned up, Scenario #1 worked correctly, it returned the correct number of records it should have.


            Thanks for the insight to Scenario #2 and how it works too! I didn't realize that the data blending works a little differently between the two scenarios.


            Thanks again!

            • 3. Re: Excel Join's in Tableau
              San M



              I am facing a similar as mentioned by Andy. I did create a relation by following Scenario#2 but still i am not getting the accurate number. I am trying to calculate attrition rate and when i am joining, it is only taking the counts from the groups where employees have termed. It should take the total employee count and not limit the counts to the group where the employees resigned. Can anyone please guide me.