4 Replies Latest reply on Apr 19, 2017 8:11 AM by Joe Oppelt

    Duplicate Item Removal

    Guy Adams

      Hi All -

       

      I've got another presumably simple solution, where I just can't seem to write the query correctly.

       

      We are able to assign multiple discounts to a singular item. However, the pull to our Tableau data source will duplicate these multiple discounts as multiple line items (see the invoice item id duplication example below; where there should only be 1 x6847300 invoice item id and subsequently only 1 entry of 49.9748 in sales (row total minus discounts)). How do I remove the duplicated invoice item id?

       

       

      Increment IdProduct NameProduct TypeDiscount NameInvoice Item IdSales
      INV50000FYOAProduct 6Product Type 5Discount 4x684730049.9748
      INV50000FYOAProduct 6Product Type 5Discount 17x684730049.9748
      INV50000FYOAProduct 15Product Type 5Discount 17x68473029.3702
      INV50000FYOAProduct 15Product Type 5Discount 17x684730146.8513
      INV50000FYOAProduct 43Product Type 2Discount 17x684730333.7425
      INV50000FYOAProduct 43Product Type 5Discount 17x684730416.8713
      INV50000FYOAProduct 66Product Type 8Discount 17x684730513.125

      Thanks for the help!

        • 1. Re: Duplicate Item Removal
          Joe Oppelt

          But it's NOT duplicate.  The data says there are two different [Discount Name]s.  What if the math for Discount 17 happened to come up with 49.99?  You would want to see that, no?

           

          It's a hack, but you could take [Discount Name] off the ROWS shelf and do AVG(Sales) instead of SUM(Sales)...

          • 2. Re: Duplicate Item Removal
            Jim Dehner

            Hi Joe

            It is probably better than a hack - looking at the data it looks like this duplication only occurs when there are multiple lines where the invoice ID and the invoice item ID are the same but their are multiple  Discount Name (I.e. unique at the Discount name field)

             

            It looks like all other combinations of unique Invoice ID, Invoice Item ID and Discount Name are unique - so using an Avg would work

             

            Jim

            • 3. Re: Duplicate Item Removal
              Guy Adams

              Hi Joe & Jim -

               

              In our POS system, both Discount 4 & Discount 17 were applied to a single purchase of Product 6 Product Type 5 within this transaction, where the sales amount for that should be $49.97. But in the push to the Tableau data server, it essentially shows it as two purchases with the same amounts.

               

              I agree that an average with the discount name removed from the row shelf works, but (and I should have specified this) I would like to show the discount name for reporting purposes. I know that removing a duplicate invoice item id would remove that discount name entirely, so in thinking about it more, I guess ideally, I would like to write something to "Average If" if there is a duplicate invoice item id, and Sum on every other unique invoice item id.

              • 4. Re: Duplicate Item Removal
                Joe Oppelt

                Guy -- Tell me more about this.  For the [Increment ID] in question, for the given product name and product type, you have two discounts, for one invoice item id.  What do you want those two rows on the report to look like?  Right now you get $49.97 on each individual row.  If you want to show both discounts, what do you expect to see?


                I CAN make it do "average if" and "SUM otherwise", but then what?