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?
I assume this is as part of a where clause. In that case you can use WHERE len(trim(Field))>0.
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,
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.
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' )
and this way:
CASE WHEN ((Date1)<(Date2) OR (Date2) IS NULL) AND ((Date1)<(Date3) OR (Date3) IS NULL) AND (Date1) IS NOT NULL THEN 'Name' END;
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.
WHERE NOT IsNull(field)
Retrieving data ...