topic Re: is not null? in QlikView

is not null?

Wed, 15 May 2013 15:48:52 GMT

Hello Community,

I am converting a report from BO into QV and in the SQL script there is "Field IS NOT NULL" in the if statement, is there a similar command in QlikView?

Thank you!

Re: is not null?

Gysbert_Wassenaar — Wed, 15 May 2013 15:50:55 GMT

I assume this is as part of a where clause. In that case you can use WHERE len(trim(Field))>0.

Re: is not null?

Wed, 15 May 2013 15:55:57 GMT

It's in the select clause:

CASE WHEN ((Date1)<(Date2) OR (Date2) IS NULL) AND ((Date1)<(Date3) OR (Date3) IS NULL) AND (Date1) IS NOT NULL THEN 'Name' END,

Re: is not null?

calvindk — Wed, 15 May 2013 16:00:52 GMT

you are stuck with If(Isnull(field)) or the len(trim(field)) > 0 that gysbert mentioned.

in general the len() one is better, since it hits more sources (excel is a good example) however, if you wanted the empty string (which is not really null) then do the isnull test.

Re: is not null?

Wed, 15 May 2013 16:04:37 GMT

Thanks for the replies. So using len:

If( ( Date1 < Date2 OR isnull(Date2) ) AND ( Date1 < Date3 OR isnull(Date3) ) AND ( len(trim(Date1)) > 0 ) , 'Name' )

Re: is not null?

Wed, 15 May 2013 16:33:24 GMT

Hi,

and this way:

YourTable:

load

//...

Name;

//...

SQL select

//..

CASE WHEN ((Date1)<(Date2) OR (Date2) IS NULL) AND ((Date1)<(Date3) OR (Date3) IS NULL) AND (Date1) IS NOT NULL THEN 'Name' END;

//from aTable;

Best

Sacho

Re: is not null?

Wed, 15 May 2013 18:01:36 GMT

Thanks, joschkoff2.

However, we create qvds from the database tables untouched and perform all the operations within the load script for the dashboard when bringing in the qvds.

Re: is not null?

Anonymous — Wed, 24 Sep 2014 15:54:03 GMT

WHERE NOT IsNull(field)