Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

How to count No.of distinct Id's?

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)
5 Replies
datanibbler
Esteemed Contributor

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

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

Not applicable

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

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.

datanibbler
Esteemed Contributor

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

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

Not applicable

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

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.

datanibbler
Esteemed Contributor

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


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