topic Set Analysis: check for isNull(field1) or isNull(field2) in QlikView

Set Analysis: check for isNull(field1) or isNull(field2)

Thu, 22 Dec 2011 00:25:44 GMT

Hi All,

Set analysis is still a mystery for me. I am trying to count the records in a table when either VASEndDate is null or when DisconnectDate is null.

I can get one or the other with this:

Count({$< VASEndDate = {"=Len(VASEndDate) > 0"} >} AcctNo)

I cannot determine how to 'or' them together. I found a post that just listed the second filed inside the <>:

Count({$< VASEndDate = {"=Len(VASEndDate) > 0"} DisconnectDate= {"=Len(DisconnectDate) > 0"}>} AcctNo)

That is not working.

By the way, is Len()>0 the best way to look for Nulls? It seems to work, but it bothers me...

Thu, 22 Dec 2011 00:39:24 GMT

The heck with set analysis for this one...

Count(if(not IsNull(DisconnectDate) or not IsNull(VASEndDate), AcctNo))

sridhar240784 — Thu, 22 Dec 2011 05:27:54 GMT

Hi,

Check if the below expression helps you.

Count({$< VASEndDate = {"=Len(VASEndDate) > 0"}> * <DisconnectDate= {"=Len(DisconnectDate) > 0"}>} AcctNo)

Check the attached application for sample.

-Sridhar

rohit214 — Thu, 22 Dec 2011 06:13:23 GMT

try this also

Count(if(IsNull(DisconnectDate)='-1' or IsNull(VASEndDate)='-1', AcctNo))

thanks&regards

rohit

Thu, 03 May 2012 12:50:57 GMT

Correct me if wrong, but I think using "LEN() > 0" would mean it is NOT Null and "= 0" or "< 1" would indicate Null