Condition for empty date field

[#1]

I have a list box that shows arrival dates. In case the database has no user entry it shows by default 01.01.1900

I tried to get rid of it with the following expression:

if(Ankunftstermin='01.01.1900','No date found')

But that just gave me:

Is there a way to show only my "No date found" without the orginal date?

Thanks a lot for your help!

Günter

[jonathandienst]

Are you doing this during LOAD or at the front end. I would do it in the reload script:

     LOAD

          ...

          If(Ankunftstermin = 0, 'No date', Ankunftstermin) As Ankunftstermin

          ...

[senpradip007]

if(Ankunftstermin='01.01.1900','No date found', Ankunftstermin) as Ankunftstermin

or

if(isnull(Ankunftstermin), 'No date found', Ankunftstermin) as Ankunftstermin

[jonathandienst]

If you insist on doing it at the front end, then a similar expression should work

     =If(Ankunftstermin = 0, 'No date', Ankunftstermin)

(assuming Ankunftstermin is a proper QV numeric date value)

[#5]

The original field is a timestamp - that's why I was using the following to create a date field in the load script:

So to answer the question: yes, I actually wanted this to happen in the front end. I tried to use

=If(Ankunftstermin = '01.01.19, 'No date', Ankunftstermin)

but that ends up in the same result, i.e. showing both '01.01.1900' and 'No date found'.

The expression "=If(Ankunftstermin = 0, 'No date', Ankunftstermin)" won't work either, as there is no "0" - it's really "01.01.1900" as a date field.

Is there a way to combine my load script to create a date out of the time stamp and mark all 01.01.1900 as "No date found"?

[sasiparupudi1 · Accepted solution]

I think you are writing the condition in the wrong tab.. write it in the expression window on the general tab

hth

Sasi

[#7]

Sasidhar is exactly right...

I was trying to apply the formula in the wrong tab:

(1)..."Formeln" is where I tried it --> WRONG!

(2)..."Allgemein" you have to select <Formel> (i.e. <Expression> as shown in Sasidhar's screenshot)

So this would be the correct way to do it:

The only thing left to do is to change the correct data format again. The "date" format from the script is gone, because a formula is used to generate the list box.

Many thanks for all your input!

[klaus_kapero]

Hello Günter,

I had a similar problem and would like highly recommend the documentation of HIC:

NULL handling in QlikView

For example I use the following logic in the Load script:

LOAD

if(len(Trim([latest date status change])) = 0, date([on list since]), date([latest date status change])) as [latest date status change],  // Entries without 'latest date status change' will be set to 'On List since date'
  
// or
  
if(len(Trim([latest date status change])) = 0, 2 , date([latest date status change])) as [latest date status change],  // 2 = 01.01.1900 because 0 = 30.12.1899

Regards

Klaus-Peter