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

[Anonymous]

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

[sujeetsingh]

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

[tresesco]

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]

sujeetsingh true

[Anonymous]

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 · Accepted solution]

In such cases I prefer to use the following expression:

Load

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

[Anonymous]

tresesco

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

Many Thanks,     Bill

[#8]

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.