topic Re: calculation with "NULL"-value in QlikView

calculation with "NULL"-value

robert_gansel — Sat, 18 Jan 2014 10:38:41 GMT

Hi community,

hope somebody can help me with this issue:

Please see following request I am doing for my db. I try to get all items where:

a) the date of "MieterEinzug" is before today

and

b) the date of "MieterAuszug" is after today

count({$<MieterEinzug={"<=$(=date(dHeute,'DD.MM.YYYY hh:mm:ss'))"}, MieterAuszug={">=$(=date(dHeute,'DD.MM.YYYY hh:mm:ss'))"}, Mietername={'Leerstand'}, wtyp={'M'}>} Mietername)

So far it works fine, as long there is a date written in the db. Actually there are some items where no date is written in the db in the column "MieterAuszug", then there is the value "NULL". As I understand it, there is no chance to calculate with NULL. So how can I check it like the following:

a) the Date of "MieterAuszug" is after today OR "MieterAuszug" isNull

Actually I know that there is a function called IsNull to proof this, but I haven't found the right way to work with it.

Hope somebody got the right idea for me!

Thanks in advance!

Re: calculation with "NULL"-value

Gysbert_Wassenaar — Sat, 18 Jan 2014 10:46:05 GMT

Perhaps like this: MieterAuszug-={"<=$(=date(dHeute,'DD.MM.YYYY hh:mm:ss'))"}

But a better solution is to replace the nulls in the script with real values: See this document: NULL handling in QlikView

Re: calculation with "NULL"-value

robert_gansel — Sat, 18 Jan 2014 10:54:25 GMT

thanks for your answer. but actually it does not help with that "-". I still do not get the items with "Null"-value

So I should replace the "null"-value with e.g. "99999" when loading the script? How shall I do this:

Actually I load this:

LOAD

str as MieterStr,

einzug as MieterEinzug,

auszug as MieterAuszug,

miete,

name as Mietername,

wtyp,

haus as Mieterhaus,

wohn as Mieternummer;

Re: Re: calculation with "NULL"-value

Anonymous — Sat, 18 Jan 2014 10:57:27 GMT

LOAD

str as MieterStr,

einzug as MieterEinzug,

if ( isnull(auszug) , 99999 , auszug ) as MieterAuszug,

miete,

name as Mietername,

wtyp,

haus as Mieterhaus,

wohn as Mieternummer;

Re: calculation with "NULL"-value

Gysbert_Wassenaar — Sat, 18 Jan 2014 10:59:37 GMT

if(isnull(auzug),... should work, but if(len(trim(auszug))=0,... will also catch strings of zero or more spaces.

Re: calculation with "NULL"-value

robert_gansel — Sat, 18 Jan 2014 11:06:37 GMT

That helps! Thanks a lot!

Re: calculation with "NULL"-value

antoniotiman — Sat, 18 Jan 2014 14:15:57 GMT

You can add in the script before loading data table

NullAsValue FieldName; (or more fields)

SET NullValue = "default value";

Regards