calculation with "NULL"-value

[robert_gansel]

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!

[Gysbert_Wassenaar]

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

[robert_gansel]

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;

[Anonymous]

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;

[Gysbert_Wassenaar]

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

[robert_gansel]

That helps! Thanks a lot!

[antoniotiman]

You can add in the script before loading data table

NullAsValue FieldName;  (or more fields)

SET NullValue = "default value";

Regards