Skip to main content

# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for
Search instead for
Did you mean:
Contributor II

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

7 Replies
Master III

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

Partner - Master

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

Master

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

Contributor II
Author

Thanks is perfect!

Contributor II
Author

Thanks Antonio, also for this solution works fine

Contributor II
Author

Another perfectly working solution. Thank you so much

Creator II

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

Community Browser