topic IsNull function returns Null in QlikView

IsNull function returns Null

orital81 — Tue, 27 Aug 2013 08:59:30 GMT

Hi,

Im getting strange behaviour from IsNull function.

When there is a value it returns 0, when there isn't it returns Null instead of -1.

Len function returns null as well.

This behaviour happens only on Access Point, not on QV Client.

Any help?

Attached the file, user / pass: qvadmin/qvadmin123

The null issue is in the GUI level.

When opening the file you will see a table with the first column called WBS.

The expression is If(IsNull(WBS),0,WBS)

It's working good when opening in QV Client, but when publishing on server and viewing on Access point

It's wrong.

Re: IsNull function returns Null

orital81 — Tue, 27 Aug 2013 09:55:13 GMT

I will appreciate any help here (-:

Re: IsNull function returns Null

Miguel_Angel_Baeyens — Tue, 27 Aug 2013 10:10:13 GMT

Hi,

I have been checking your file, but there is no indication on where the issue is happening? I guess it's in the script, but not sure. Can you please elaborate?

Note that IsNull() requires a parameter, either it be an expression or a field. If the field does not exist when you load it, then you get an error instead of a -1, not because the field is not null, but because it does not exist. Same may happen in the front end.

Miguel

Re: IsNull function returns Null

hic — Tue, 27 Aug 2013 10:38:14 GMT

In addition to what Miguel points out, I just want to add that IsNull() when used in charts may in fact in some circumstances seem to return NULL: If you have missing values of type two, the expression is never calculated for these chart cells. So basically it looks as if IsNull() has returned NULL, when it in fact has not been calculated at all.

See more in NULL handling in QlikView

HIC

Re: IsNull function returns Null

orital81 — Tue, 27 Aug 2013 11:27:35 GMT

Hi Miguel,

Let me clarify the issue:

The null issue is in the GUI level.

When opening the file you will see a table with the first column called WBS.

The expression is If(IsNull(WBS),0,WBS)

It's working good when opening in QV Client, but when publishing on server and viewing on Access point

It's wrong.

Re: IsNull function returns Null

Anonymous — Sat, 22 Mar 2014 11:34:57 GMT

did you ever figure this one out? we have just upgraded from 10 to 11.2 and are facing a similar issue where if(isnull(FIELD_NAME) ,1,FIELD_NAME)) works in the client but on the access point it always returns null

Re: IsNull function returns Null

rbecher — Sat, 22 Mar 2014 20:34:17 GMT

Hi Ori,

I think it's probably better to solve this in script..

- Ralf

Re: IsNull function returns Null

Anonymous — Sat, 22 Mar 2014 21:26:55 GMT

I stopped using IsNull() years ago. Try if this helps:

If(len(trim(WBS))=0,0,WBS)

Re: IsNull function returns Null

rbecher — Sun, 23 Mar 2014 18:05:00 GMT

Right, but doesn't help either if the expression is not evaluated at all becaus of missing values.