topic Re: Exclude duplicates in QlikView

Exclude duplicates

Wed, 22 Aug 2012 06:47:21 GMT

I have to fields, NumberofChildren and ID.

Because the ID occurs multiple times the sum(numberofchildren) shows wrong. I want to distinct them so it only sum it ones.

And, does that don´t have a children are marked as 0.

How can I do that?

Re: Exclude duplicates

sreenivas — Wed, 22 Aug 2012 06:55:55 GMT

In the Expression instead of Sum use ONLY

Re: Exclude duplicates

its_anandrjs — Wed, 22 Aug 2012 07:11:48 GMT

Hi,

Use Distinct with Sum function like Sum(Distinct numberofchildren).

Thanks Rgds

Re: Exclude duplicates

Wed, 22 Aug 2012 07:22:25 GMT

Hello ,

the possible is two: from script from report:

from script

TAB:

noconcatenate

LOAD ID,

NAME,

AVG(Number of cildren) ------------------------>(OR dISTINCT( nUMBEr OF cILDREN))

Resident INL

group by

ID,

NAME;

FROM REPORT:

IN THE EXPRESSION oNLY(Number of cildren) OR sum(distinct Number of Cildren)

Re: Exclude duplicates

Wed, 22 Aug 2012 07:25:41 GMT

=Only({$}NumberOfChildren) works but how do I then sum them? I´d like to se the total number of children.

Edit: sum(distinct Number of Cildren) wont work because the it only count the value of say 2 children ones, but several person can have 2 children.

If 2 people have 2 children I want to get the result of 4. The problem is that I some how have to distinct the ID in the sum so it only count the children ones per person.

Re: Exclude duplicates

swuehl — Wed, 22 Aug 2012 07:47:12 GMT

Try

=sum({$-< NumberOfChildren = {0} >} aggr(NumberOfChildren, ID))

Re: Exclude duplicates

Wed, 22 Aug 2012 07:48:02 GMT

Why not do something simple like : -

Sum(NumberOfChildren) / Count(ID)

Re: Exclude duplicates

Wed, 22 Aug 2012 07:56:16 GMT

Thanks swuehl. Thats perfect!