is not null?

[#1]

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!

[Gysbert_Wassenaar]

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

[#3]

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,

[calvindk]

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.

[#5]

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' )

?

[#6]

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

[#7]

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.

[Anonymous]

WHERE NOT IsNull(field)