7 Replies Latest reply: Jun 27, 2017 11:02 AM by Massimo Leu

# Count distinct

Hello,

I need you help. I have the following table

ObjectYes/No
Object1
Object1
Object1NO
Object1NO
Object1SI
Object1SI
Object2
Object2
Object2NO
Object2SI
Object3
Object3
Object3NO
Object4
Object4
Object4NO
Object5
Object5
Object6
Object6
Object7

How can I count distinct object in such a way that:

If the object is "Yes" then "Yes" if the object is "Yes" and "No" then Yes, if the object is only "NO" then "NO" otherwise "NULL"

The final result should be:

 Yes No Null Object 2 2 3

Thanks

• ###### Re: Count distinct

Hi Massimo,

LOAD Object,MaxString([Yes/No]) as YN group by Object;
[Yes/No]
FROM
"https://community.qlik.com/message/1295545"
(html, codepage is 1252, embedded labels, table is @1);

Table :

Dimension  YN

Expression  Count(YN)

Regards,

Antonio

• ###### Re: Count distinct

Thanks Antonio, also for this solution works fine

• ###### Re: Count distinct

Hi,

May be like this (look attached file)

Table1:

If([Yes/No]='SI', 2,

If([Yes/No]='NO', 1,

0

)) as Flag;

[Yes/No]

FROM

(html, codepage is 1251, embedded labels, table is @1);

NoConcatenate

Table2:

Object,

If(Max(Flag)=2, 'SI',If(Max(Flag)=1,'NO', 'NULL')) as State

Resident Table1

Group By Object;

DROP Table Table1;

Result:

State,

Count(State)

Resident Table2

Group By State;

DROP TAble Table2;

Regards,

Andrey

• ###### Re: Count distinct

Thanks is perfect!

• ###### Re: Count distinct

Hi Massimo,

Try:

Yes

=count({\$<[Yes/No] = {'SI'}>}Distinct Object)

No

=count({\$<[Yes/No] = {'NO'}>*<Object = E({<[Yes/No] = {'SI'}>})>}Distinct Object)

Null

=count(Distinct Object) - count({\$<[Yes/No] = {'SI','NO'}>}Distinct Object)

Cheers

Andrew

• ###### Re: Count distinct

Another perfectly working solution. Thank you so much

• ###### Re: Count distinct

Hi Massimo,

yourTable:

Object,

"Yes/No",

if("Yes/No" = 'Yes', 1, 0) as isYes,

if("Yes/No" = 'No', 1, 0) as isNo

From Sourceblablabla;

then in a pivot table, use Object as the dimension (row) and add 3 measures (columns):

count(Aggr(count({\$<isYes={1},isNo={0}>} Object) + count({\$<isYes={1},isNo={1}>} Object), Object))  // yes

count(Aggr(count({\$<isYes={0},isNo={1}>} Object), Object)) // No

count(Aggr(count({\$<isYes={0},isNo={0}>} Object), Object)) // Null

Hope this helps