3 Replies Latest reply on Jul 24, 2018 11:16 AM by Joshua Milligan

    Joins not working

    Praveen Ettam

      When you have 10 records in table A and 20 records in Table B and if you use Join (intersection)  you should get zero to Max 20 records.  But in many cases it is giving 200 records,  do you guys know what is happening?  And when you create output the number is almost 3-4 times.

        • 1. Re: Joins not working
          Joshua Milligan

          Praveen,

           

          It is not impossible to get 200 records of data from a join of 10 records to 20 records.  In fact, there are multiple cases where this is exactly what would happen.  Would you be able to share more about your data and the way you've configured the join?

           

          Best Regards,

          Joshua

          1 of 1 people found this helpful
          • 2. Re: Joins not working
            Praveen Ettam

            If you see the Clean 4 has 257K and Clean 5 has 39K,  when I am doing left join it should not exceed 39K,  Why would it get number all the time 1,048,576 all the time?  Just my experience with Prep is so far horrible. It is better if Tableau team start webinar every week and discuss the issues.

             

            • 3. Re: Joins not working
              Joshua Milligan

              Praveen,

               

              I can definitely understand your frustration.  I do think in this case it is likely the data itself that is throwing things off.  A left join does not grantee that you won't end up with more rows than the left hand side (by the way, the left in your screenshot is the purple - Clean 4 - so, I would at least expect 257K rows as a result)

               

              Here's an example:

               

              Let's say you have an Orders table that looks like this:

               

               

              5 distinct orders, each having a customer.  In this case, we can see that there are 3 distinct customers and I might want to join in a customer table.  Normally, I would assume that each order has only one customer, so I might try to join in my customer table.  But, what if it looks like this:

               

               

              The issue is that I have duplicate customers (specifically ID #3)  Which means that every row in the first table could potentially find more than one match (in this case 6), so worst-case I might have 6 times the resulting rows from what I expect.

               

              And in fact, I do end up with more records than the left-side would have had me believe:

               

               

              I suspect that something similar is happening to you.  One way you can check to see if you have duplicates that are causing multiple (unexpected) results from the join, you might add an aggregation that groups by the fields you are joining on (cono and vendno in your case) and sums the Number of Rows.

               

               

              In the result, you should not see anything in the values of Number of Rows except 1.  If you do, you'll want to de-duplicate the data (see How to remove duplicate records in Tableau Prep | VizPainter ) before the join.

               

              Hope this helps!

              Joshua

               

              I'd also be willing to take a look at your specific data set if needed....

              2 of 2 people found this helpful