Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now

A common recurring question on the QlikCommunity forum is around dates that don’t work. Here follows a help on fixing the three most common causes. If you encounter such a question on the forum, just link to this post in your answer.

1. Incorrect Date Interpretation

When data is loaded into QlikView, dates are often read as strings. QlikView then tries to recognize a pattern in the string that looks like the date format specified in the DateFormat environment variable. This sometimes fails and then you need to use the Date#() function to help QlikView understand that it is a date.

How do I know that a date is correctly interpreted? That’s easy. Just format it as a number and see what you get. (List box properties – Number – Integer)

ListBoxNumberFormatting.png

 

The question is now what your list box looks like. If you have a number which is roughly 40000 (usually right-aligned), then you are all set. But if you still have a date stamp (usually left-aligned), then you need to use the Date#() function in the script. See QlikView Date fields.

DateCheck.png

 

2. Linking integer dates with fractional dates

You have a date in two different tables, and you want to use this date as a key, but it doesn’t seem to work. Then you should suspect that you have true dates (integers) in one table and timestamps (fractional numbers) in the other, but the formatting of the dates hides this fact.

How do I know whether this is the case? That’s easy. Just format it as a timestamp and see what you get. (List box properties – Number – TimeStamp)

ListBoxTimeStampFormatting.png

 

The question is now what your list box looks like. If you have timestamps where hours, minutes and seconds are all zero, then you are all set. But if you have numbers in these places, then you need to use the Floor() function in the script to get integer dates. See QlikView Date fields.

TimeStampCheck.png

 

3. Incorrect date comparisons

The most subtle error is however the one with timestamps in comparisons, e.g.

     … Where Date = '2011-12-31';

Will this work? Yes, provided that the date format inside the string is recognized by QlikView, i.e. that it corresponds to the date format specified in the environment variable DateFormat in the beginning of the script.

It becomes even more complex if you use variables. Then it is important to use quotes correctly. The following will work:

     Let vToday = Today();
     … Where Date = '$(vToday)';

… but the following will not:

     … Where Date = $(vToday);

The reason is that the $(vToday) will expand to a string containing the date, and then the comparison will be e.g.

     … Where Date = 2/19/2013;

So the date (which is approximately 40000) will be compared to 2 divided by 19 divided by 2013, which of course is not what you want.

My recommendation is to always use numeric variables for dates. They always work - quotes or no quotes:

     Let vToday = Num(Today());
     … Where Date = $(vToday);

See more on QlikView Date fields.

HIC

22 Comments
Partner
Partner

It is very good blog to understand on date functionality.

7,522 Views

Concise and well explained. Thanks!

0 Likes
7,522 Views
Partner
Partner

After reading this I understood the  Date functionality correctly.

Nice Blog.

0 Likes
7,522 Views
Not applicable

Hi Henric,

Its another one of your great entries on the nuances of qlikview! Thank you for the clarifications.

However I face a slightly different problem. I have a date string like 'Mon, 06/01/2014 20:10:35 GMT'. How do I construct the format string in the date#/timestamp# function in order to read in such a format??

I tried: 'WWW, DD/MM/YYYY hh:mm:ss GMT' but it did not work. In particular I am unable to interpret the weekday using Date#/Timestamp#. What am I doing wrong here? Or am I supposed to use a different function altogether?

Thank you for your help in advance!

Thyagesh

0 Likes
7,522 Views

There are two problems with your format string. The first is the weekday and the 'WWW' code. For some reason this doesn't work and I suspect that the reason is that you can only use the 'WWW' in formatting functions and not in interpretation functions. But I need to check this with the developers to see how it is implemented to be sure.

The second is the 'GMT'. Time zones are not supported in the format code - it just has never been implemented. (I'll push for it. It ought to be there.)

So the work-around you need to use is;

  Mid(Date,26,3) as Timezone,

  Timestamp#(Mid(Date,6,19),'DD/MM/YYYY hh:mm:ss') as Timestamp,

If you don't want to hard-code the numbers in the Mid() functions, you can use the Index() function to find the proper position of the blankspace character. Finally, you can combine the Timestamp and the Timezone to create a new time, e.g.

  ConvertToLocalTime(Timestamp, Timezone) as LocalTime,

HIC

0 Likes
7,522 Views
Contributor II
Contributor II

This is awesome. Thanks! The Time/Stamp and Time formatting was like a light bulb going off for me.

0 Likes
7,522 Views