3 Replies Latest reply on Dec 10, 2018 7:26 AM by Ciara Brennan

    Best way to join these two tables

    Pablo Gabatto

      Hello community,

       

      I have struggled with this for a few days and am requesting some help. I have two tables that I get as separate exports from two different platforms. Please be patient with me as I am newish to Tableau and especially to table joins.

       

      The problem I am experiencing is when I tried a right join, using Tableau Prep and the 3PL data as the right (side) data source. I get duplicated values so when I add up the cost of the item it's incorrect. I've attempted other joins and get the same result. I am trying to calculate how many unique orders there are and how many units (denoted by column O Line Item Qty on the 3PL Data sheet) were sold.

       

      I'm using Tableau Desktop 2018.03. Attached is a packaged workbook.

       

      Thanks so much for any help.

        • 1. Re: Best way to join these two tables
          Mavis Liu

          Hi Pablo,

           

          It looks as if your store order ID isn't unique, so you'll need to either bring in a unique identifier (which will give it a one to one match) or use other fields in additional to the store order ID in order to make the join unique.

           

          I have found a field called Line Item Name which may work:

           

          2018-12-07_21h20_18.png

           

          Thanks,

           

          Mavis

          • 2. Re: Best way to join these two tables
            Pablo Gabatto

            Hi Mavis,

             

            To add some more context, the "store order id" is unique. The "store order id" is on two rows because if the order has two skus, one sku is on one row and the second sku is on the second row, with the "store order id" being repeated for each sku. This is what is causing some problems for me.

             

            I am also seeing the same thing occur when from my 3PL. The "store order id" is on three or more rows since there can be three objects with that order, ie the main product, the box for the product, a sticker and a marketing insert.

             

            I have read through some guides and it appears I have a many-to-many relationship and perhaps I need to do data blending but I am not currently familiar with that method. Hoping for some guidance on this forum.

             

            Thanks for any suggestions and help.

            • 3. Re: Best way to join these two tables
              Ciara Brennan

              Hi Pablo

              Just fyi on the topic of Tableau Prep there is a Dev Office Hours session tomorrow that may be of interest. It's on Tableau Prep Conductor, further details including the registration link available below by Amanda Boyle

               

              Dev Office Hours: Keep your data Fresh with Tableau Prep Conductor

              Dev Office Hours.jpg

               

              Hope this helps,

              Ciara

              [Program Manager - Tableau Community]