6 Replies Latest reply: Dec 6, 2013 7:33 AM by Steve Dark RSS

    [MyDateField] select for Today() or Null()

    Bill Markham

      Hi

       

      I have a date field, called say [MyDateField].  In a Text Box Action I wish to select where [MyDateField] is either Today() or Null().

       

      Any suggestions on how to approach this ?

       

       

      Best Regards,     Bill

        • Re: [MyDateField] select for Today() or Null()
          Nicole Smith

          For select where the field is today (notice there are no brackets around the field name):

           

           

          QlikView doesn't let you select null values.  If you want to be able to select them, you'll need to populate them with something in the script so that they aren't null.

            • Re: Re: [MyDateField] select for Today() or Null()
              Bill Markham

              Nicole

               

              When you say

              "QlikView doesn't let you select null values.  If you want to be able to select them, you'll need to populate them with something in the script so that they aren't null."


              you have got to the crux of my problem.



              I have a date field and I am at a loss as to what I could populate it with instead of Nulls.  A spoof date of say a hundred years in the future or in the past will not be acceptable.  A value of 'Missing' would be ok, but I doubt I would be able to put that in a date field,



              Best Regards,     Bill

                • Re: Re: Re: [MyDateField] select for Today() or Null()
                  Nicole Smith

                  What about a simple hyphen?  (see attached)

                    • Re: Re: Re: [MyDateField] select for Today() or Null()
                      Bill Markham

                      Nicole

                       

                      I must admit that I was skeptical about your 'simple hyphen' suggestion, but it works perfectly !!!

                       

                      Somehow the QlikView Date Format field accepts the 'simple hyphen' and yet still behaves correctly as date linking to my Master Calendar.

                       

                      So yup, your answer is Correct .

                       

                       

                      I've still got a couple of Helpful 's on offer as I would love to know why the QlikView Date Format field accepts the 'simple hyphen' and still behaves correctly.

                       

                       

                      Many Thanks,     Bill

                        • Re: [MyDateField] select for Today() or Null()
                          Nicole Smith

                          I would think it would work no matter what you decide to put in the field.  If it's not a date, it just won't link to your Master Calendar, but all of the dates should still link.  If you have an example where that isn't true, please share it, and I can take a look.

                          • Re: Re: Re: [MyDateField] select for Today() or Null()
                            Steve Dark

                            Hi Bill,

                             

                            Data values in QlikView are not strongly typed - so it is possible to have strings, numbers and various formats of each in the same field and not have any ill effects.  Where you need to be careful with the hyphen solution outlined above is when there is a chance that the first row that QlikView sees when loading is a hyphen rather than a date.  QlikView will then decide to treat all rows as string and will probably show decimal numbers for all your other dates.

                             

                            There is a fudge to avoid this, simply loading a inline table with a date in the correct format ahead of your main data load (so the first row seen is in the correct format) but that is not a pretty way of doing things.

                             

                            If the hyphen approach works in your scenario then there is no reason why you shouldn't make use of this.

                             

                            - Steve