A common problem occurs with dates in QlikView when an underlying system or database stores dates as strings or even as separate day, month and year fields rather than true dates. For example, if the dates are stored as strings, subtracting one from the other won't give you the number of days between the two. So ideally we need to convert the badly stored dates to true dates when we load them into QlikView, and luckily there are easy methods to solve this problem using a group of functions known as "interpretation functions".
Let's look first at how to create a true date from a string. Luckily, QlikView has a simple interpretation function that solves this problem for us. The date#() function interprets a string using the given format and creates a true date from it. Take special note of the #, its not a typo and differentiates it from the formatting function date() which we looked at in Part 1. Its straightforward to use as follows:
date#(DateStringField, 'YYYY-MM-DD') AS DateField
The function interprets the field DateStringField using the format YYYY-MM-DD and if it can do so, outputs a true date. Remember from Part 1 that QlikView stores dates as a dual value; the numerical representation of the date and a string representing how the date will be presented to the user. One thing worth noting about the date#() function is that the resulting date will be formatted using the same format used to interpret the string, which might not be the format you desire your dates to be displayed in. You should also remember that we can use the date() function to format the string part of a true date and so we can add it to the above code as follows:
date(date#(DateStringField, 'YYYY-MM-DD'), 'DD-MM-YYYY') AS DateField
Working with timestamps stored as strings is made just as simple in QlikView using the timestamp#() function instead. It works in exactly the same way and can be used like this:
timestamp#(TimestampStringField, 'YYYY-MM-DD hh:mm:ss') AS TimestampField
Again, you can wrap it in the timestamp() function if you need to format the resulting timestamp differently.
Great, we've solved the first problem, or have we? What if the field contains dates stored as strings of more than one format. For example, I may have dates stored as a mixture of formats such as YYYY-MM-DD, DD-MM-YYYY and DD/MM/YYYY. As with lots of things in QlikView, there is more than one solution but the simplest is a very underused QlikView function. The alt() function returns the first value passed to it that is numerical, and because a true date in QlikView is a dual value (part of which is a number) we can use it to return the first date#() format that manages to create a true date.
alt(date#(DateString, 'YYYY-MM-DD'), date#(DateString, 'DD-MM-YYYY'), date#(DateString, 'DD/MM/YYYY'), 'No Date Found') AS Date
If the first date#() function manages to interpret the string as a true date, then it will be returned, if not then it will move on to the next, and so on. If no numerical value is found, the alt() function will return the last value in the parameter list and so in this example, if it can't interpret the string using any of the given formats then it will return the string "No Date Found".
OK, so now we've resolved dates stored as strings, but what about our other problem of dates split between separate day, month and year fields. You might not see this as a problem at all, we often split up a date in QlikView into the day, month and year to make it simpler to use in charts, but it is vital to also have it at as true date if you want to be able to perform calculations with it. This is thankfully a really easy problem to solve using the makedate() function. It accepts up to 3 parameters, the year, the month (if omitted then Jan is assumed) and the day (if omitted the 1st is assumed) and creates a true date from them.
makedate(YearField, MonthField, DayField) AS Date
And of course there is also a maketime() function which can be combined with the makedate() function as follows to create a timestamp:
makedate(YearField, MonthField, DayField) + maketime(HourField, MinuteField, SecondField) AS Date
Remember that dates are integer (whole number) values for the number of days past the QlikView epoch date and time is a fraction of a day, and so we can simply add the two values together to create the timestamp. However, note that because we have used the + operator, QlikView assumes that you are only interested in a numerical answer and so we need to wrap the entire thing in the timestamp() function to format it correctly as a dual value like this:
timestamp(makedate(YearField, MonthField, DayField) + maketime(HourField, MinuteField, SecondField)) AS Date
Hopefully from this you can see that its relatively simple to create a true date from just about any way a date can be store in a database or other underlying system.
In the next part we'll look at how to perform more manipulations and complex calculations using dates, such a splitting up dates into separate fields, working out if a date is within the month to date, year to date, last year to date etc.