Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

how to include only a specific row from duplicates?

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:

expired_only1.PNG

Table2:

expired_only2.PNG

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

expired_only3.PNG

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

16 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

try using a Set expression like this:

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

-Rob

http://masterssummit.com

http://robwunderlich.com

Anonymous
Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Not applicable
Author

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
Not applicable
Author

Correction**

How to fetch IDS that have "Expired Counterpart".

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Creator III
Creator III

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
Not applicable
Author

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
Creator III
Creator III

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.