Qlik Community

Qlik Design Blog

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

Get the Dates Right

QlikView has an intelligent algorithm to recognize dates independently of which region you are in. In most cases, you will have no problems loading them. It just works and you do not need to think about it. However, in some cases dates are not properly recognized and then you need to add some code in the script to make it work.

 

Dual dates.jpgFirst of all – there are no data types in QlikView. Instead QlikView uses dual data storage for all field values; every field value is represented by a string and – if applicable – a number. The task of the developer is to make sure that QlikView recognizes the date correctly so that both a textual and a numeric part of the date are created.

 

The numeric part of a date is a serial number (same as Excel), i.e. a number around 41000 for dates in the year 2012.

 

Here are some tips that will help you load dates correctly and hopefully help you better understand how the date handling works.

 

  1. Use the interpretation functions
    If you have the date as text you may need to use an interpretation function, e.g. Date#() or Timestamp#().

  2. Nest functions
    If you want to display the date a specific way, you may need to nest an interpretation function inside a formatting function, e.g. Date(Date#(DateField, 'YYYYMMDD'), 'M/D/YY').

  3. Use the MakeDate function
    If you have Year, Month and Day as separate fields, use the MakeDate() function to create a date serial number.

  4. Use the rounding functions
    If you have a timestamp and you want a date, you should probably use a rounding function, e.g. Date(Floor(Timestamp#(DateTimeField, 'YYYYMMDD hh:mm:ss')), 'M/D/YY').

  5. Use the numeric value in variables
    If you want to use the variable for comparisons, it is simpler to use the date serial number rather than the textual representation, e.g. Let vToday = Num( Today() ).

  6. Use combination fields, e.g. Year and Month as one field
    It is often practical to display both year and month in one field, e.g. Date(MonthStart(DateField),'YYYY-MMM')

  7. Use the Dual function
    If you want more complicated combinations of a string with an associated numeric value, you can do almost anything using the Dual() function.

  8. Use the Alt function for fields with mixed date formats:
    If you have a field with mixed date formats, you can resolve them using the Alt() function.

 

For a more elaborate description of these tips, see the technical brief on QlikView dates.

 

HIC

12 Comments
mellerbeck
Contributor II

Hi Henric, what about a discussion about Qlikview and Nulls

This guy seems to have a down a good job, but I'm sure there is more to be added

http://www.qlikviewaddict.com/2012/04/handling-nulls-in-qlikview.html

0 Likes
4,065 Views

That could be a good idea. I'll take a look at it. And I am open for other suggestions also, if you have other ideas.

HIC

0 Likes
4,065 Views
mellerbeck
Contributor II

A good primer on using Qlikview with source control would be interesting (that might already exist)

4,065 Views
Not applicable

Even you can use de floor function.

4,065 Views
vivientexier
Contributor II

In order to get the numeric value of a date it seems better to multiply it by 1 :

13/05/2014 * 1

What do you think ? Num() is just format.

0 Likes
4,065 Views

Well, they are both "just format".

The dual format has both a textual member and a numeric member: (<text>,<number>). If you for example have a date (2014-05-13,41772), then both Num(Date) and Date*1 will result in (41772,41772), since the multiplication will use the numeric part of the dual as input.

I would still recommend using Num(Date) though, mainly since it is more legible: A developer that sees your code one year later, may not understand why you do a Date*1, but he will understand what the purpose of a Num(Date) is.

HIC

4,065 Views
ecolomer
Honored Contributor II

Good work

0 Likes
4,065 Views
mrooney
New Contributor III

Hi Henric,

I really love to always apply the shortest possible function in my scripts in order to make easier the maintenance. So

I understand the syntax of this nested function you exposed is the best one:

     Date(Floor(Timestamp#(DateTimeField, 'YYYYMMDD hh:mm:ss')), 'M/D/YY').

But, I wonder if this 'shortened' way would be syntactically correct for Qik engine too:

          Date(Date#(Floor(DateField),'YYYYMMDD'),'M/D/YY')

My point is: Will Qlik 'see' only the date part using just Floor(DateField)?

Thank you.

M.

0 Likes
4,065 Views

First, there is the automatic date interpretation (See Automatic Number Interpretation) that tries to interpret the DateField. (I.e. convert from a text to a dual.) If it succeeds, you don't need to wrap it in the Date#() function. And if it doesn't, you must use the Date#() function. (And it will never succeed to automatically interpret a YYYYMMDD date.)

Secondly, if a function expects a numeric parameter, it uses the numeric part of the parameter. And if the function expects a string, it uses the textual part of the parameter.

This means that the Floor() function always uses the numeric part of the date. It also means that the combination Date(Date#(Floor(DateField))) doesn't make sense, since Floor() is evaluated before the Date#(). Either DateField is automatically interpreted, and then you can use Date(Floor(DateField)). Or it isn't, and then you must use Date(Floor(Date#(DateField,'YYYYMMDD'))).

HIC

4,065 Views
ilanbaruch
Contributor III

very helpful thank you

0 Likes
4,065 Views
Chanty4u
Esteemed Contributor III

nice ...simple

0 Likes
4,065 Views
Steveo250k
New Contributor II

While this is a bit old now, and I'm using Qlik Sense, this is useful.

I am troubled by "tries to" and "in most cases".  The thing about Qlik is it usually fails gracefully.  Meaning, I don't always know if there is an error in my logic until further down the line.

It seems, when working with dates at least, always use one of the above casting or date methods to put your value into a known (and thus documented) state before trying to use it.  Never assume.

0 Likes
2,858 Views