3 Replies Latest reply on Aug 4, 2016 11:26 PM by Sandhya Vissa

    Excel Join's in Tableau

    andy.yu

      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
            andy.yu

            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
              Sandhya Vissa

              Hi,

               

              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.

               

              Thanks
              Sandhya