Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

galileumax
New 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
Honored Contributor III

Re: Count distinct

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
Honored Contributor

Re: Count distinct

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
Honored Contributor

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

galileumax
New Contributor II

Re: Count distinct

Thanks is perfect!

galileumax
New Contributor II

Re: Count distinct

Thanks Antonio, also for this solution works fine

galileumax
New Contributor II

Re: Count distinct

Another perfectly working solution. Thank you so much

mattquinnterex
Contributor II

Re: Count distinct

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