topic load field with empty values in QlikView

load field with empty values

chriscools — Sun, 19 Jun 2011 09:40:04 GMT

Hello,

i'm loading an excel-file with a field "status" some of the records contain no value in this field "status".

if i make a list box in qlikview it's impossible to select the records without a value in this field because it doesn't give the empty value to choose.

how can i solve this?

do i need to do this in the load statement by replacing the "null"value by something else?

and how should this be done? or is there another way?

thanx!

grtz,

chris

load field with empty values

chriscools — Sun, 19 Jun 2011 09:56:37 GMT

hello,

i already found the answer!:

if ( is null (status) , 'empty', status) as status1,

load field with empty values

prieper — Sun, 19 Jun 2011 20:46:01 GMT

Think that ISNULL may not always deliver correct results on 64bit-computers. Function may be replaced by LEN(TRIM(Status)) = 0.

HTH
Peter

load field with empty values

chriscools — Mon, 20 Jun 2011 07:22:36 GMT

Hello,

I don’t work on a 64bit computer at the moment, but i’ll keep it in mind!

Could you also explain what every piece of the function just does? So I understand a bit better.

Thanx!

Chris

load field with empty values

prieper — Mon, 20 Jun 2011 14:00:20 GMT

Hi Chris,

am not such a techie, but think that there is a different interpretation of the NULL-value in 32- and 64-bit systems, thus the check with ISNULL may deliver different results.

The LEN-function simply checks the length of a fieldcontent and always returns an integer, in case of no entry (NULL) it will show "0". TRIM-function is just to safeguard that also a series of blanks would be treated like NULL.

Peter