4 Replies Latest reply on Sep 24, 2018 8:26 AM by Jonathan Brough

    How do I add values for unique entries (based on an ID)?

    Ann-Margaret Javier

      Hello!

       

      I'm using Tableau Desktop 10.2

       

      I have a data source that is joined as following; all left joins:

      Data Base Set Up.png

       

      This causes an issue for me as I'd like to get the total of my pipeline (yellow box). Right now, because of the way I've joined everything, my pipeline is huge since all the ID's are being added:

       

      IDValue in ($M)
      1234100
      567850
      567850
      1234100
      1234100
      910120

       

      Right now, my total value is $420M, when it should be $170M.

       

      I'd like to keep the joins because my dashboards contain filters I'd like to affect worksheets.

       

      Any idea on what I can do?

        • 1. Re: How do I add values for unique entries (based on an ID)?
          Jonathan Brough

          Hi Ann-Margaret,

          You likely have a cartesian product occuring because there a many-to-many relationship is occuring between Pipeline and Datsource 5, or between Datasource 5 and those tables to the right of it.

          Please see the attached thread that identifies and advises on this. https://community.tableau.com/message/445670#445670

          It advises that you use Custom SQL to perform a group by with an aggregation of Value ($), such as min() or max().

          Thanks,

          Jonathan

          • 2. Re: How do I add values for unique entries (based on an ID)?
            suresh.gooty

            first change all to equi join where id=id then check the total.... after that change to left outer one by one.... in one of the join you are using left so duplicate id's are forming in the table and then if this duplicate table join with another table as left outer automatically your records will get doubled.... in your example it should be 170... but almost three times of 170 means.... 170*3=510... so you made two joins as mistake ... those two joins should be equi join instead of left outer join.... take data into excel and add one by one table then you will come to know where exact problem exists...... this is the procedure to debug..... good that you did not get Cartesian product. if data is huge your db will blast.... hope you understand and this information is useful for you.

            • 3. Re: How do I add values for unique entries (based on an ID)?
              Ann-Margaret Javier

              Hi Jonathan Brough

               

              That's exactly it. My pipeline is joined to individual product ID's. And multiple products can be sold in a single invoice. I wanted the sum of my invoices, but since they're joined to multiple products, I'm getting multiples back.

               

              How do I use a custom SQL? Sorry, completely new to this, and I think I've been a little over-ambitious as to what I can and can't do on Tableau...

              • 4. Re: How do I add values for unique entries (based on an ID)?
                Jonathan Brough

                Hi again,

                Once you have a database connection established you should see the 'New Custom SQL' option on the left hand panel.

                You should be able to drag that in to the workspace and position it to the right of your Pipeline and Datasource 5.

                You will then get a text box to write your Custom SQL out in, which I presume would need Union of the far right tables and a join onto the Datasource 5 table. This Custom SQL can be previewed using the button in the bottom left.

                After that, you will lastly need to select your join condition between the Pipeline table and this new Custom SQL created dataset, in the same way you have been creating up to now.

                Hope this helps,

                Jonathan