Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Thanks is perfect!
Thanks Antonio, also for this solution works fine
Another perfectly working solution. Thank you so much
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