Skip to main content
Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
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
MVP

In such cases I prefer to use the following expression:

Load

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

7 Replies
Master III

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

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.

Specialist III
Anonymous
Not applicable
Author

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

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.

Community Browser