how to include only a specific row from duplicates?

[Anonymous]

I have the following requirement: Table1 is my trans table and table2 has status info on ProductIDs. I want to count only those productID which has only Expired status not Created. In table2, you can see all IDs have 2 statuses except for XYZ004 & XYZ006. How do i set up syntax on the front end so that it counts only 004 & 006. I have ----->

count(DISTINCT(if(Status='EXPIRED','ProductID'))) and it is not bringing the correct result.

Table1:

Table2:

My current syntax is showing this result. how to achieve right Result?

[rwunderlich]

try using a Set expression like this:

count({<Status={EXPIRED}>}DISTINCT ProductID)

-Rob

http://masterssummit.com

http://robwunderlich.com

[Anonymous]

Rob,

I used your expression: count({<Status={EXPIRED}>}DISTINCT ProductID) and it is still giving me the same result.

because prodIDs have a duplicate status with "EXPIRED", it is counting those instances. I want it to count when ProdID has only 1 status and that Status is 'EXPIRED'

[rwunderlich]

My mistake. Are your only two statuses EXPIRED and CREATED? Or are there more. What I'm thinking is that you could check for not CREATED like:

count({<Status=-{CREATED} DISTINCT ProductID)

-Rob

[Anonymous]

Hi Rob, thanks for your reply. It is still counting 10 instances when a prodID with Expired status occurs. It seems it is counting those IDs that have created & expired status. How to make it count only those that have Expired but not Created.

count({<Status=-{CREATED} DISTINCT ProductID) this expression is counting IDS that are not created; i.e. Expired. Since 8/10 IDs have an "Expired Counterpart" the total count is 10 (:. 2 IDs that have only Expired) How to fetch IDS that have "Created Counterpart". Meaning, to be pulled into the table, it should have only Expired, not a "Created Counterpart"

Thanks again.

[Anonymous]

Correction**

How to fetch IDS that have "Expired Counterpart".

[rwunderlich · Accepted solution]

Another way of saying this would be to exclude anything that has a Status of anything besides EXPIRED?

count({<ID=E({<Status-={EXPIRED}>})>}ID)

or another way, if you wanted ID selections to be honored:

count({<Status={EXPIRED}, ID={"=count(DISTINCT Status)=1"}>}ID)

-Rob

http://masterssummit.com

http://robwunderlich.com

[timanshu]

I think, The Products  with On-Shelf value as Yes  and Status as Expired will give the result.

But if it is not the case, then as you required, this expression will work:

=if(aggr(Count(DISTINCT Status),ProductID)=1,count({<Status={'EXPIRED'}>}ProductID))

[Anonymous]

Hi Rob & Timanshu,

both of your solutions worked, when I added Distinct Clause to the expression:

Rob:

count({<ProductID=E({<Status-={EXPIRED}>})>}Distinct ProductID)

count({<Status={EXPIRED}, ProductID={"=count(DISTINCT Status)=1"}>}Distinct ProductID)

Timanshu:

=if(aggr(Count(DISTINCT Status),ProductID)=1,count({<Status={'EXPIRED'}>} Distinct ProductID))

Can you please explain the logic in your expression. specifically: =if(aggr(Count(DISTINCT Status),ProductID)=1, (this part) I am trying to understand the expression and how Qlik calculates.

[timanshu]

Hi,

Aggr function will group values by ProductId and count(distinct status) will give the count of distinct values corresponding to that ProductId. So, if count is 1 that means the ProductId is either repeated with same status or productid is not repeated.

if you still dont understand the logic. Don't worry you will eventually gain.You probably only need to understand aggr function functionality.