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: 
galileumax
Contributor II
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:

   

YesNoNull
Object223

Thanks

7 Replies
antoniotiman
Master III
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

ahaahaaha
Partner - Master
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

effinty2112
Master
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

galileumax
Contributor II
Contributor II
Author

Thanks is perfect!

galileumax
Contributor II
Contributor II
Author

Thanks Antonio, also for this solution works fine

galileumax
Contributor II
Contributor II
Author

Another perfectly working solution. Thank you so much

mattquinnterex
Creator II
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