Count distinct

[galileumax]

Hello,

I need you help. I have the following table

Object	Yes/No
Object1
Object1
Object1	NO
Object1	NO
Object1	SI
Object1	SI
Object2
Object2
Object2	NO
Object2	SI
Object3
Object3
Object3	NO
Object4
Object4
Object4	NO
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

[antoniotiman]

Hi Massimo,

LOAD Object,MaxString([Yes/No]) as YN group by Object;
LOAD 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

[ahaahaaha]

Hi,

May be like this (look attached file)

Table1:

LOAD*,

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

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

0

)) as Flag;

LOAD Object,

    [Yes/No]

FROM

[https://community.qlik.com/thread/265597?sr=inbox&ru=249257]

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

NoConcatenate

Table2:

LOAD

Object,

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

Resident Table1

Group By Object;

DROP Table Table1;

Result:

LOAD

State,

Count(State)

Resident Table2

Group By State;

DROP TAble Table2;

Regards,

Andrey

[effinty2112]

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

[galileumax]

Thanks is perfect!

[galileumax]

Thanks Antonio, also for this solution works fine

[galileumax]

Another perfectly working solution. Thank you so much

[Anonymous]

Hi Massimo,

Do it from your data load editor, for example:

yourTable:

Load

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