Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
ZoeM
Contributor III

Only Unique Occurring Items to be listed

Hi Community,

I am probably overthinking this situation, but I need to figure out how to write an expression that will count ONLY the unique items that occurred Once with a value of 1.

In the attached QVW, I have 8 Items of which two occurred once each, Juice and Cream. I would like to only show these two items and I cant seem to figure how to do it.

 

any help rendered will be truly appreciated as always.

1 Solution

Accepted Solutions
mrybalko
Contributor II

Re: Only Unique Occurring Items to be listed

Hello 

Try this:

count({<Item = {"=aggr(count(Item), Item)=1"}> * <Value= {'1'}>} Item)

P.S. I recommend to precalculate special flags in load script. Aggr is heavy memory and cpu function.

6 Replies
mrybalko
Contributor II

Re: Only Unique Occurring Items to be listed

Hello 

Try this:

count({<Item = {"=aggr(count(Item), Item)=1"}> * <Value= {'1'}>} Item)

P.S. I recommend to precalculate special flags in load script. Aggr is heavy memory and cpu function.

ZoeM
Contributor III

Re: Only Unique Occurring Items to be listed

That did the trick, I will take a look at the expression you created and see if I can formulate it for my dashboard. 

Thanks Community

ZoeM
Contributor III

Re: Only Unique Occurring Items to be listed

What if...

Another dimension was thrown in, say region, and I want to see what item is still constantly at value 1? It would still be Cream and Juice, but I cant seem to manipulate your expression to capture this.

The result should be:

Cream and Juice perform consistently regardless of area.

mrybalko
Contributor II

Re: Only Unique Occurring Items to be listed

You should add every new dimension in aggr function:

count({<Item = {"=aggr(count(Item), Item, Region)=1"}> * <Value= {'1'}>} Item)

Recommend this post and example

ZoeM
Contributor III

Re: Only Unique Occurring Items to be listed

Thanks for the suggestion.

However, I added another item called Mushroom and gave it a 1 value for NA, 2 value for EU and 3 value for SA. My result for the initial question i snow showing Cream, Juice and Mushroom. I should only have Cream and Juice, Mushroom had other values in other regions (2 in EU and 3 in SA). 

ZoeM
Contributor III

Re: Only Unique Occurring Items to be listed

forgot to attach the dashboard