5 Replies Latest reply: Jun 11, 2014 4:54 AM by Friedrich Hofmann RSS

    How to count No.of distinct Id's?

    ashok Velisetty

      Hi All,

      DateIdStatus
      6/1/20141011
      6/1/20141012
      6/1/201410144
      6/1/201410145
      6/2/20141022
      6/2/20141023
      6/2/201410244
      6/2/20141025
      6/3/20141031
      6/3/20141032
      6/3/20141043
      6/4/20141045
      6/4/201410445
      6/4/20141049
      6/5/20141051

      This is my sample input data:

       

       

      Here i need to count the distinct id's count   Logic is:

      1)for one day if one id contains status=44 then "Pick schedule" count is 1 and " Pick not schedule" is Zero.

      if the same id does'nt contain 44 then "Pick not schedule" is 1.

       

      2) for one day if one id contains status=45 then "Deli schedule" count is 1 and " Deli not schedule" is Zero.

      if the same id does'nt contain 45 then "Deli not schedule" is 1.

       

      3)for one day if one id doesn't contain status 44 and 45 then "Piick not schedule" is 1 and "Pick schedule " is zero

      and "Delivery Schedule" is zero and "Delivery not schedule" is 1.

       

      I Need below output:

       

      DateStatusId CountThis for our understanding not to show
      6/1/2014Pick schedule1
      Pick not schedule0
      Delivery schedule1
      Delivery Not schedule0
      6/2/2014Pick schedule1
      Pick not schedule0
      Delivery schedule0
      Delivery Not schedule1
      6/3/2014Pick schedule0
      Pick not schedule2(id=103,104)
      Delivery schedule0
      Delivery Not schedule2(id=103,104)
      6/4/2014Pick schedule0
      Pick not schedule2(id=104,105)
      Delivery schedule1(id=104
      Delivery Not schedule1(Id=105)
        • Re: How to count No.of distinct Id's?
          Friedrich Hofmann

          Hi Ashok,

           

          I don't quite understand your problem.

          - You have (in the example 6/1/2014) four lines with the same ID, but different stati.

          - Status 44 is present once, so "Pick"=1 and "Not pick"=0

          - Status 45 is present once, so "Deli"=1 and "No Deli"=0

          <=> However, there are two more lines where the status is neither of the two, so here "Not pick" and "No Deli" would
                  be 1 and the other ones would be 0, so that in the end each would be 1, no?

          Do you want those lines counted or not?

           

          Best regards,

           

          DataNibbler

            • Re: How to count No.of distinct Id's?
              ashok Velisetty

              Thank for ur reply,

               

              here if one day(6/1/2014) status mark as 44 and 45 then no need to check other status for the same day, take count of "Not pick" and "Not Deli" is zero only.

                • Re: How to count No.of distinct Id's?
                  Friedrich Hofmann

                  Hi Ashok,

                   

                  well, in that case, to do it in one step, I would

                  - create two more fields, a binary field (1 or 0) - two of those, one for the status 44 and one for the status 45, right?

                  => Aggregate the table with the expression  >>> sum([field_for_status_44]) <<< and the same for 45.

                     => Mind, you need a GROUP BY clause in the same LOAD where you have these expressions, containing all
                          fields that you do not aggregate (all but those two summed-up fields)

                  => That will give you a table with either 1 or 0 (I assume it cannot be more than one), which tells you if the status 44
                        or status 45 is present in the lines for that ID.

                  HTH

                   

                  Best regards,

                   

                  DataNibbler

                    • Re: How to count No.of distinct Id's?
                      ashok Velisetty

                      Tx,

                       

                      so then if id not having both(44 and 45) then also we need to take count for "Not pick'=1 and "Not deli"=1,how we take those count if mark binary 0.

                      and there is chance to one id getting same status means either 44 or 45 may get twice or thrives  for the same day.

                       

                      plz send me the code.

                        • Re: How to count No.of distinct Id's?
                          Friedrich Hofmann


                          Hi,

                           

                          I'll begin from the end: There can be no confusion if you create two separate fields, one that returns 1 only if 44 was found, otherwise 0 - and one returning 1 if 45 was found, otherwise 0.

                          => That would be two lines in your LOAD:

                          - >>> IF([Status] = 44, 1, 0) as 44_is_present <<<

                          - >>> IF([Status] = 45, 1, 0) as 45_is_present <<<

                           

                          You can then sum those up, including the product_ID and the day in your GROUP BY

                          => That would be >>> GROUP BY [date], [product_ID]; <<< (as the last line of your LOAD in the script; That means
                                you get one line per day per product_ID))

                          => Then you will, in each of the fields, get a 1 (or possibly more) if the respective status is present for one specific
                                 product_ID on one specific day, otherwise you'll get 0

                          => Then you can go on based on that.

                          Best regards,

                           

                          DataNibbler