7 Replies Latest reply: Sep 14, 2015 10:51 AM by Klaus-Peter Roethke RSS

    Condition for empty date field

    Günter Angerer

      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

        • Re: Condition for empty date field
          Jonathan Dienst

          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
            Pradip Sen

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

            or

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

            • Re: Condition for empty date field
              Jonathan Dienst

              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
                Günter Angerer

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



                  • Re: Condition for empty date field
                    Sasidhar Parupudi

                    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

                      • Re: Condition for empty date field
                        Günter Angerer

                        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!

                      • Re: Condition for empty date field
                        Klaus-Peter Roethke

                        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