Null fields are not selectable.

[Coolavin]

Hi,

Null fields do not seem to be selectable.  I have tried several ways to evaluate the null fields but it doesn't work.

I have tried these different ways below.

=If(IsNull(CredentialExpireDate), 'Active', 'Inactive')

=If(IsNull([CredentialExpireDate]) = -1, 'Active', 'Inactive')
=If(len(CredentialExpireDate)<=2 , 'Active', 'Inactive')

=If([CredentialExpireDate]=' ' , 'Active', 'Inactive')
=If([CredentialExpireDate]='- ' , 'Active', 'Inactive')

When I uncheck the "include null values" box these fields go away telling me that it views these fields as nulls however non of the logic used to evulate nulls works. 

Is there something I am doing wrong with evaulating a null field?

[ThePeterK]

Is there a reason this is a chart function and not done through the load editor->replace values? That's where I'd go first.

[asinha1991]

there is a difference between null and missing value, it could be that you are using a dimension for which CredentialExpireDate doesn't exist, this cannot be handled with isnull…are you using dimensions from two different data tables?

it could also be that your if else is reaching dead end, can you share your full condition?

[Coolavin]

Thank you for pointing that out about the null vs missing value. Would that affect the length evaluation as well? 

[Coolavin]

Is there a way to evaluate fields with missing values?

[JGMDataAnalysis]

Check this...

https://community.qlik.com/t5/QlikView-Documents/NULL-handling-in-QlikView/ta-p/1484472

[asinha1991 · Accepted solution]

the way I do it is by using count with aggr

if(aggr(alt(count(CredentialExpireDate),0),primarykeyfieldofothertable)=0,'Inactive','Active').

maybe there is a better way

[Coolavin]

Wow thank you! this works! 

Can you tell me what is happening here? Is it checking the primary key of the table? 

[asinha1991]

it is trying to count instances of your field by primary key(identified by grouping with your existing dimensions( of other table.

if it is 0 or null(in this case it returns null as you are using aggr)  then no common instance exist

len and isnull wont work directly on the missing value