topic Re: Only Unique Occurring Items to be listed in QlikView

Only Unique Occurring Items to be listed

ZoeM — Sat, 16 Nov 2024 04:28:27 GMT

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.

Re: Only Unique Occurring Items to be listed

mrybalko — Wed, 13 Feb 2019 08:47:56 GMT

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.

Re: Only Unique Occurring Items to be listed

ZoeM — Wed, 13 Feb 2019 11:53:47 GMT

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

Re: Only Unique Occurring Items to be listed

ZoeM — Wed, 13 Feb 2019 19:27:47 GMT

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.

Re: Only Unique Occurring Items to be listed

mrybalko — Fri, 15 Feb 2019 07:57:09 GMT

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

Re: Only Unique Occurring Items to be listed

ZoeM — Fri, 15 Feb 2019 15:10:20 GMT

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

Re: Only Unique Occurring Items to be listed

ZoeM — Fri, 15 Feb 2019 15:12:27 GMT

forgot to attach the dashboard