Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

bill_markham
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

Tags (4)
1 Solution

Accepted Solutions
tresesco
Not applicable

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

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
sujeetsingh
Not applicable

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

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

tresesco
Not applicable

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

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

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

bill_markham
Not applicable

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

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

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

In such cases I prefer to use the following expression:

Load

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

bill_markham
Not applicable

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

tresesco

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

Many Thanks,     Bill

Not applicable

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

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.