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: 
Anonymous
Not applicable

IsNull(MyField) vs. Len(MyField) = 0

Hi

IsNull(MyField) and Len(MyField) = 0 seem to do pretty much the same, as in they can both be used to check if a field is empty.

Does anybody know if they are fully interchangeable or are they in some way different ?

Best Regards,     Bill

1 Solution

Accepted Solutions
tresesco
MVP
MVP

In such cases I prefer to use the following expression:

Load

If( Len(Trim(Field))=0 or Isnull(Field) , this, that) as FieldName

View solution in original post

7 Replies
sujeetsingh
Master III
Master III

isnull() can give -1 value too but len can not give negative value see the sample i have attached

tresesco
MVP
MVP

Hello Bill,

A good point to think of. See, here an interesting analysis that would clear doubts.

Len(Null()) ->0

Len('') ->0

Isnull('') ->0  // which is FALSE  !!!!!!

Hence you can conclude both are NOT fully interchangeable.

agni_gold
Specialist III
Specialist III

Anonymous
Not applicable
Author

tresesco

So you are saying that '' is not exactly the same as Null().

Would I be correct in thinking that on a database :

  • Null() is where nothing has inserted / updated anything in the field
  • '' could be where something was inserted / updated and then later that text deleted in the End User GUI.

What I am after is creating a Flag where the field is empty, so I think it would be better for me to check using Len(MyField) = 0 as that covers all 'emptiness', as opposed to IsNull(MyField) which would only flag true nulls.

Is my understanding correct ?

Best Regards     Bill

tresesco
MVP
MVP

In such cases I prefer to use the following expression:

Load

If( Len(Trim(Field))=0 or Isnull(Field) , this, that) as FieldName

Anonymous
Not applicable
Author

tresesco

Yup, I see what you are doing there and that certainly does look like a robust method.

Many Thanks,     Bill

Not applicable
Author

The below expression covers all the scenerio's like Null & Empty Fields validation:

IF(LEN(TRIM(FieldName),'Field neither Null nor Empty' , 'Field either Null or Empty') AS FieldCheck

Generally in the Qlikview, the 0 value is always False and any other no is True.