3 Replies Latest reply on Aug 25, 2017 6:45 AM by Tom W

    Select Distinct Row

    Sanjiv Kumar

      I have a table full of duplicate products with same product ID which can be selected by doing "{FIXED [ID]: MIN([ID])}" However, there are other products with no id or blank id's or "- " in the place of ID.

      using the calculation above it also selects all the blank ones as one and all the null ones as one. how do I exclude this id and have them as separate rows?

        • 1. Re: Select Distinct Row
          Sujay Paranjpe

          Hi Sanjiv,

           

          What do you mean by how do I exclude this id and have them as separate rows?

          Do you want to exclude as well as have them or do you want all of them to be represented as one category like "Öthers"?

           

          Try creating calcuated field like below

          [Wrapped Prod ID]

          If <Product_ID_col> = "" or If <Product_ID_col> = " -" or If <Product_ID_col> = <any other value>

               "Others

           

          ELSE

               <Producy_ID_col>

          END

           

          Now change your LOD as {FIXED [Wrapped Prod ID]: MIN([Wrapped Prod ID])}

          Let me know if it works

           

          Best,

          Sujay

          Mark answer correct if it solves your problem.

          • 2. Re: Select Distinct Row
            Sanjiv Kumar

            Thanks Sujay for your reply.

             

            I meant I want to count the number of unique products in the database and I am doing that using the Product ID.

            However, that calculation is also grouping the products with no id or "-" instead of product ID

             

            I don't want those to be grouped and counted as separate products. if that makes sense

            • 3. Re: Select Distinct Row
              Tom W

              COUNTD({FIXED [ID]: MIN(IF [ID] != "" THEN [ID] END)})