topic How to identify uniqueness as an expression? in QlikView

How to identify uniqueness as an expression?

Wed, 21 Aug 2013 11:46:38 GMT

I would like to create a column in a straight table to identify uniqueness.
For example, I have apples, bananas & pears. I also have the names of people that bought them.

How can I calculate against each fruit how many people only bought that fruit?

Many thanks,

Re: How to identify uniqueness as an expression?

Wed, 21 Aug 2013 11:56:54 GMT

rowno() or recno()

Re: How to identify uniqueness as an expression?

er_mohit — Wed, 21 Aug 2013 11:57:01 GMT

try this expression

aggr(nodistinct count(People),Fruitfield)

Re: How to identify uniqueness as an expression?

Wed, 21 Aug 2013 12:05:51 GMT

Hi Vishwa

Can you plz explain in detail how to acheive the task

Re: How to identify uniqueness as an expression?

richard_pearce6 — Wed, 21 Aug 2013 12:06:25 GMT

Try

=sum(if(Aggr(Count(Fruit),Name)=1,1))

Re: How to identify uniqueness as an expression?

nizamsha — Wed, 21 Aug 2013 12:07:48 GMT

TABLE1:

LOAD * Inline [

FRUITS,PEOPLE,CITY

APPLE,5,TRE

ORANGE,6,BAN

GRAPES,7,COM

APPLE,3,MAN

];

TABLE2:

Load FRUITS, SUM(PEOPLE )as ArtNoTotal Resident TABLE1 group by FRUITS;

fruits as dimension and ArtNoTotal as expression

Re: How to identify uniqueness as an expression?

Wed, 21 Aug 2013 12:17:09 GMT

Aggr(count(People), Fruitfield)

if no. of count is 1 then it means unique.

Re: How to identify uniqueness as an expression?

Wed, 21 Aug 2013 12:36:10 GMT

Thank you for all your responses, but I am obviously missing something.

My straight table has Fruit as it's dimension.

if I do aggr(count(distinct Name), Fruit), I get the same as count(distinct Name), which is the number of people who bought this fruit.

What was the aggr function supposed to add to this?

If 2 people bought Apples and 3 bought Pears, but only one bought both, then I want to return 1 against Apples and 2 against Pears, that being the unique purchasers of each fruit.

Re: How to identify uniqueness as an expression?

richard_pearce6 — Wed, 21 Aug 2013 13:11:03 GMT

Hi,

If the Fruit is the dimension then aggr by fruit won't make any difference to the sum. In my example its aggr by Name and only counts if the count of fruit is 1. Ie. A person only bought one fruit (that fruit) and no others

Can you feedback on my example? I thought reading your question the result was correct?

Richard

Re: How to identify uniqueness as an expression?

Wed, 21 Aug 2013 13:44:20 GMT

Thank you Richard. The results I got at first with your suggestion seemed wrong, so I incorrectly thought that you had got the fields the wrong way round.

In fact all that was missing was a distinct:

sum(if(aggr(count(distinct Fruit),Name)=1,1)) did the trick.

Much appreciated

Alan Cobbald