Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
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
];