topic Re: IsNull(MyField) vs. Len(MyField) = 0 in QlikView

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

Anonymous — Mon, 02 Dec 2013 11:15:31 GMT

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

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

sujeetsingh — Mon, 02 Dec 2013 11:32:46 GMT

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

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

tresB — Mon, 02 Dec 2013 11:45:22 GMT

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.

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

agni_gold — Mon, 02 Dec 2013 11:46:23 GMT

sujeetsingh true

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

Anonymous — Mon, 02 Dec 2013 12:23:16 GMT

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

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

tresB — Mon, 02 Dec 2013 12:32:14 GMT

In such cases I prefer to use the following expression:

Load

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

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

Anonymous — Mon, 02 Dec 2013 12:35:37 GMT

tresesco

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

Many Thanks, Bill

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

Mon, 02 Dec 2013 13:23:02 GMT

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.