topic Re: Where Field is Null not working in QlikView

Where Field is Null not working

Fri, 06 Sep 2013 12:53:03 GMT

hello,

I have a table with articles, and each article has a code. this code is stored in another column.

Articles	Code
Article 1	12345
Article 1
Article 1
Article 2	2345
Article 2

I try to only select the fields that have a code, but somehow I cannot select Null.

I tried this:

[FactuurNummerLeverancierTemp]:
LOAD
  invs1_ref as [DocumentNumber],
  invscs_order_no as [FactuurNummerLeverancier];
SQL SELECT * FROM INVS_DATA
WHERE INVS1_CO_SITE = '01'
  AND invscs_order_no IS NOT NULL
      AND INVS1_REC_TYPE = 2;

Not working.

I also tried len() > 0 in a resident load. nope

Please help.

Re: Where Field is Null not working

Fri, 06 Sep 2013 12:54:50 GMT

I think I found the problem.

Each empty field contains a number of empty spaces.

' '

How can I exclude them?

Re: Where Field is Null not working

swuehl — Fri, 06 Sep 2013 12:58:08 GMT

Within a QV Load statement, you can use trim() function.

Re: Where Field is Null not working

tresB — Fri, 06 Sep 2013 12:58:14 GMT

Try len() with trim(), like:

Len(trim(invscs_order_no))>0

Re: Where Field is Null not working

Brice-SACCUCCI — Fri, 06 Sep 2013 12:58:40 GMT

Hi,

This is an SQL statement and is executed directly on the database.

'SELECT * FROM INVS_DATA

WHERE INVS1_CO_SITE = '01'

AND invscs_order_no IS NOT NULL

AND INVS1_REC_TYPE = 2;'

Can you check the behaviour of the statement on your database using a SQL query tool?

Or, if you want to use the resident load after your initial SQL load, try Len( Trim( Field ) ) > 0.

Regards,

Brice

Re: Where Field is Null not working

Anonymous — Mon, 04 Nov 2013 17:46:22 GMT

I want select the values NULL and with the sentence "IS NULL" work fine