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

how to include only a specific row from duplicates?

Anonymous — Mon, 25 May 2015 22:56:24 GMT

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?

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

rwunderlich — Mon, 25 May 2015 23:06:40 GMT

try using a Set expression like this:

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

-Rob

http://masterssummit.com

http://robwunderlich.com

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

Anonymous — Mon, 25 May 2015 23:25:05 GMT

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'

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

rwunderlich — Mon, 25 May 2015 23:37:49 GMT

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

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

Anonymous — Tue, 26 May 2015 00:14:33 GMT

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.

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

Anonymous — Tue, 26 May 2015 00:16:01 GMT

Correction**

How to fetch IDS that have "Expired Counterpart".

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

rwunderlich — Tue, 26 May 2015 04:17:59 GMT

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

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

timanshu — Tue, 26 May 2015 08:12:35 GMT

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

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

Anonymous — Tue, 26 May 2015 16:48:12 GMT

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.

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

timanshu — Tue, 26 May 2015 18:35:07 GMT

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.

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

rwunderlich — Tue, 26 May 2015 18:45:07 GMT

Is the aggr() even required in this case? ProductID is already the chart dimension?

-Rob

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

timanshu — Tue, 26 May 2015 19:38:52 GMT

I actually taken ProductID and Status both as dimension. And also if for a product id having multiple records with status as expired, then it will count multiple times. but ideally it should count only 1 for single productid.

Also, same goes with the expression you suggested. If for a product id having multiple records with status as expired, then it will count multiple times.

I know this may not happen in scenario that John faced. So, both expressions are nice to apply.

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

Anonymous — Wed, 27 May 2015 05:38:48 GMT

hi Rob, Thanks again for the solution you provided. In my effort to understand your work, I have the following interpretation from your expressions. Any comments/corrections will encourage me.

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

Here, the Expn is telling to exclude all non-Expired ProdIDs & distinctly count the remainder ProdIDS.

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

here it should be understood that consider only those Records with Expired Status & within that, distinctly count those ProdIDs that have only one type of status (is this equivalent to the part of your expn: ProductID={"=count(DISTINCT Status)=1"}??

or : consider only those Records with Expired Status & within that, distinctly count those ProdIDs that occur with only 1 type of status..?

Many Thanks.

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

timanshu — Wed, 27 May 2015 05:56:35 GMT

Yes. Exactly.

I request you to kindly mark the Question as answered. It will help others to easily determine the right answer.

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

Anonymous — Wed, 27 May 2015 14:12:37 GMT

Timanshu, Done

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

rwunderlich — Wed, 27 May 2015 18:09:55 GMT

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

The comma in the Set Expression should be read as "AND". Think of the set expression (everything between {}) as selections.

Select Status=EXPIRED AND count(of Statuses)=1, then count(DISTINCT ProductID)

-Rob

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

Anonymous — Wed, 27 May 2015 20:29:55 GMT

Thanks Rob