topic Re: Condition for empty date field in QlikView

Condition for empty date field

Mon, 14 Sep 2015 12:42:51 GMT

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

Re: Condition for empty date field

jonathandienst — Mon, 14 Sep 2015 12:45:31 GMT

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

...

Re: Condition for empty date field

senpradip007 — Mon, 14 Sep 2015 12:46:10 GMT

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

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

Re: Condition for empty date field

jonathandienst — Mon, 14 Sep 2015 12:47:21 GMT

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)

Re: Condition for empty date field

Mon, 14 Sep 2015 13:05:42 GMT

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"?

Re: Condition for empty date field

sasiparupudi1 — Mon, 14 Sep 2015 13:18:26 GMT

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

hth

Sasi

Re: Condition for empty date field

Mon, 14 Sep 2015 14:12:50 GMT

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!

Re: Condition for empty date field

klaus_kapero — Mon, 14 Sep 2015 14:51:26 GMT

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