Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted

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

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

1 Solution

Accepted Solutions

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

What about a simple hyphen?  (see attached)

6 Replies

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

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

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

What about a simple hyphen?  (see attached)

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

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

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.

MVP & Luminary
MVP & Luminary

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

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