Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
slacayo
Contributor III
Contributor III

Unique Count Where Columns are not null no overlap

My table:

LOAD * INLINE [
 Id, cat1, cat2, cat3, cat4, cat5 
1 abc efg - - -
2 - - - - -
3 - - - - y
4 - - - - - 
5 a - - - - ]; 

 

I want to create a KPI which takes a subset of columns (cat1, cat2, cat3) and if in either of the 3 columns, a row contains a non-null value, I want to take the count of ids which do so. So for example, the kpi from the table above would be

 

unique uids without nulls in cat1, cat2, or cat3: 2 

Labels (1)
2 Replies
Digvijay_Singh

Not sure if its the best way - 

Count(distinct if(Coalesce(cat1,0) = 0 and Coalesce(cat2,0) = 0 and Coalesce(cat3,0) = 0,0,1))

Digvijay_Singh

Used the below script for testing - 


set NullInterpret= '';

nullcheck:
LOAD * INLINE [
Id,cat1,cat2,cat3,cat4,cat5
1,abc,efg,
2
3,,,,y
4
5,a
];