topic Re: [MyDateField] select for Today() or Null() in QlikView

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

Anonymous — Tue, 03 Dec 2013 17:08:47 GMT

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 — Tue, 03 Dec 2013 18:33:38 GMT

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()

Anonymous — Tue, 03 Dec 2013 19:04:37 GMT

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 — Tue, 03 Dec 2013 19:36:45 GMT

What about a simple hyphen? (see attached)

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

Anonymous — Wed, 04 Dec 2013 13:45:32 GMT

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 — Thu, 05 Dec 2013 18:11:00 GMT

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()

stevedark — Fri, 06 Dec 2013 12:33:17 GMT

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