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: 
eduardo_dimperio
Specialist II
Specialist II

Set Analysis Problem

Hi,

i have a set analysis that not work. Whats wrong please?

count({<DIA0=-{}>} distinct OID_METER) Not recognize Null

IF (ISNULL(DIA0),'EMPTY') Recognize Null

set_analysis.JPG

1 Solution

Accepted Solutions
sunny_talwar

How about this

Count({<OID_METER = {"=Len(Trim(DIA0)) = 0"}>} DISTINCT OID_METER)

View solution in original post

12 Replies
sunny_talwar

Null value cannot be compared to anything in set analysis... why don't you try this

Count({<DIA0 = {"*"}>} DISTINCT OID_METER)

eduardo_dimperio
Specialist II
Specialist II
Author

Hi Sunny, that a little strange, because i have been used this code for a while, but in my new version of an app it not working anymore. But i need to count OID_METER where DIA0 are null. Count({<DIA0 = {"*"}>} DISTINCT OID_METER) doesn't work

sunny_talwar

How about this

Count({<OID_METER = {"=Len(Trim(DIA0)) = 0"}>} DISTINCT OID_METER)

krishna_2644
Specialist III
Specialist III

u can try all- 

1.   IF (ISNULL(DIA0), Count(distinct OID_METER) )

2.   Count( distinct   If(DIA0 = null() ,OID_METER ))

3.   Count({$<DIA0 ={'*'}-{''}>} OID_METER)   -  this expression counts non-null values

4.    Count({$< DIA0 = {"=Len(Trim(DIA0))=0"} >}  DISTINCT  OID_METER )   - Set expression that counts     distinct  OID_METER  where DIA0 is null


PS - i didnt test these expressions.

eduardo_dimperio
Specialist II
Specialist II
Author

Sunny, that works with a small change, its not = that i need, its <>.

Count({<OID_METER = {"=Len(Trim(DIA0)) <> 0"}>} DISTINCT OID_METER)

So, about that, why doesn't work count({<DIA0= - {}>} distinct OID_METER) ?

sunny_talwar

I thought you said you wanted to count OID_METER where DIA0 is null?

Capture.PNG

eduardo_dimperio
Specialist II
Specialist II
Author

Sorry about that, my mistake, but you know why Count({<DIA0= - {}>} distinct OID_METER)  doesn't work?

sasiparupudi1
Master III
Master III

i think it is because your data is having nulls and the empty set is not going to filter the nulls..

When you use len in conjunction with trim, it is going to eliminate both nulls and empty records

Try the following and if it works as intended then the above statment should be correct

Count({$< DIA0 -= {'=IsNull(DIA0)=-1'} >}  DISTINCT  OID_METER )

eduardo_dimperio
Specialist II
Specialist II
Author

Hi Sasidhar, not work even changing to

Count({$< DIA0 -= {'=IsNull(DIA0)=0'} >}  DISTINCT  OID_METER )

or

Count({$< DIA0 = {'=IsNull(DIA0)=-1'} >}  DISTINCT  OID_METER )