topic Count distinct in QlikView

Count distinct

galileumax — Tue, 27 Jun 2017 10:19:53 GMT

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

Re: Count distinct

antoniotiman — Tue, 27 Jun 2017 13:57:43 GMT

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

Re: Count distinct

ahaahaaha — Tue, 27 Jun 2017 14:12:09 GMT

Hi,

May be like this (look attached file)

Table1:

LOAD*,

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

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

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

Re: Count distinct

effinty2112 — Tue, 27 Jun 2017 14:46:58 GMT

Hi Massimo,

Try:

Yes

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

=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

galileumax — Tue, 27 Jun 2017 14:59:34 GMT

Thanks is perfect!

Re: Count distinct

galileumax — Tue, 27 Jun 2017 15:02:09 GMT

Thanks Antonio, also for this solution works fine

Re: Count distinct

galileumax — Tue, 27 Jun 2017 15:12:44 GMT

Another perfectly working solution. Thank you so much

Re: Count distinct

Anonymous — Tue, 27 Jun 2017 15:21:31 GMT

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