Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Condition for empty date field

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

Date_01.png

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

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

But that just gave me:

Date_02.png

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

Thanks a lot for your help!

Günter

1 Solution

Accepted Solutions
sasiparupudi1
Master III
Master III

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

hth

Sasi

Untitled.jpg

View solution in original post

7 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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

          ...

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
senpradip007
Specialist III
Specialist III

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

or

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

jonathandienst
Partner - Champion III
Partner - Champion III

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)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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

Date_03.png

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
Master III
Master III

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

hth

Sasi

Untitled.jpg

Not applicable
Author

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)

Date_04.png

So this would be the correct way to do it:

Date_05.png

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
Contributor II
Contributor II

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