load field with empty values

[chriscools]

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

[chriscools · Accepted solution]

hello,

i already found the answer!:

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

[prieper]

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

HTH
Peter

[chriscools]

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

[prieper]

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