Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
qlikdash
Valued Contributor

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
MVP & Luminary
MVP & Luminary

Re: how to include only a specific row from duplicates?

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
MVP & Luminary
MVP & Luminary

Re: how to include only a specific row from duplicates?

try using a Set expression like this:

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

-Rob

http://masterssummit.com

http://robwunderlich.com

qlikdash
Valued Contributor

Re: how to include only a specific row from duplicates?

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'

MVP & Luminary
MVP & Luminary

Re: how to include only a specific row from duplicates?

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

qlikdash
Valued Contributor

Re: how to include only a specific row from duplicates?

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.

qlikdash
Valued Contributor

Re: how to include only a specific row from duplicates?

Correction**

How to fetch IDS that have "Expired Counterpart".

MVP & Luminary
MVP & Luminary

Re: how to include only a specific row from duplicates?

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

timanshu
Contributor III

Re: how to include only a specific row from duplicates?

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))

qlikdash
Valued Contributor

Re: how to include only a specific row from duplicates?

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

Re: how to include only a specific row from duplicates?

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.