Thursday, 9 August 2012

Dates in QlikView - Part 2

In Part 1 of this series about dates in QlikView, we looked at how dates are stored in QlikView and how we can manipulate and perform some basic conversion on them. Those principals are critical to working with dates in QlikView and I'd strongly recommend you read it before continuing with this part.

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.

17 comments:

  1. This is awesome! Thank you for laying it out so clear and concise! Have you considered covering the ever interesting how many days/months/years between two dates?

    This was one approach I read about on the forums
    =((year(today(2))*12)+month(today(2))) - (((year([Set up date])*12)+month([Set up date])))

    for how many months between two dates.

    From here http://community.qlikview.com/thread/47580

    ReplyDelete
    Replies
    1. Hi Michael

      I'm glad you found it of use and you've happened to guess some of the topics for part 3.

      I looked at calculating the number of days between 2 dates in part 1. This is simple, just subtract one true date from another.

      Calculating the months between 2 dates is a little more tricky however. As you've obviously worked out, unfortunately there is no DateDiff function in QlikView as there is in SQL and VBScript, although it is on the wish list for a future release. The method you mentioned is the one I normally use and in part 3 I'll be covering a number of topics around dates including their use in variables. As part of this I'll show how to create a "MonthDiff" function using a variable with passable parameters as a replacement for a proper DateDiff.

      Calculating the number of years between 2 dates in the same way as DateDiff would is simple as follows:
      =year(today())-year(DateField)
      Again, one thing I will touch on in part 3 is how there can be more than one definition of the logic behind what we consider to be a year having passed. For example, does 365 days have to have passed between 2 dates or just the calendar year change. Watch this space :)


      Regards
      Matt

      Delete
  2. Nice one. Matt is the part-3 is released or writing in progress ? Intrested in knowing how to find months/weeks etc between 2 dates.

    ReplyDelete
  3. Hi

    Part 3 is still being written but should be released in the next few days. I've the outlines of parts 4 and 5 also so they will follow at some point.

    As Michael mentioned above you can calculate the number of months between 2 dates using the following formula:

    =((year(FirstDate)*12)+month(FirstDate)) - (((year(SecondDate)*12)+month(SecondDate)))

    And you can use the same method and substitute multiplying the year by 52 (number of weeks a year) and using the week() function instead of the month() function.

    Hope that helps
    Matt

    ReplyDelete
  4. Hello,

    After reading through your post I feel you could be able to help me with the issue I'm facing.

    I load data from an excel source which has a key date field. A dashboard requires to display the information in the Q1-2012, Q2-2012 format. The problem is our company's financial year is from April - March, so the Q1 should begin from April.

    I tried a few options but failed to get it to meet my needs. Will you be able to suggest a method of getting the QQ-YYYY format?

    Any help is much appreciated.

    Thanks!

    ReplyDelete
  5. Hey,

    Can anybody please tell me how to calculate an age of a person with respect to a "certain date".

    I mean....If the BirthDate field has 01/28/1970 & and the present date (i.e. Today's Date) is 7/30/2015. But I want to know the age of a Person when the CertainDate field is 03/14/2004.

    P.S.- What should be the calculation keeping in mind that the CertainDate is in a different related table other than the BirthDate.

    ReplyDelete
    Replies
    1. Hi Leroy
      There are several answers to your question, and choosing which is the correct for you will depend upon your exact requirement. Assuming both dates are being stored as true dates then subtracting one from the other will give you the difference in days. Dividing this number by 365 will give you an approximation.

      floor((DateField1 - DateField2) / 365)

      Assuming you are doing this in an expression and not the script then it should make no difference that the dates are in separate tables. This method doesn't account for leap yours though thus why I said there are several ways depending on what you wish to achieve.

      Regards
      Matt

      Delete
    2. Hi
      can we convert the system date to persian date in qlikviw

      Delete
    3. Hi
      I don't believe there are any inbuilt functions to convert from a Gregorian calendar to a Persian calendar. The simplest method would be to use a conversion table loaded in and connected to the date field of your choice. Using the dual function you could still store a numeric "days since epoch" but display the Persian date making them sort correctly etc.

      Regards
      Matt

      Delete
  6. Hi
    I am trying to load data from a SQL server using a OLE DB connection and would like to convert the data in the columns suffixed with the word date from YYYYMMDD to DD/MM/YYYY format. Please Help.
    Self-learning Qlikview Newbie
    The script I am using is
    SQL SELECT AccountingYearMonth as AccountingPeriod,
    AdmiDate AS StartDate,
    Age,
    SpellDisDate AS DischargeDate,
    SpellDominantProcedure AS DominantProcedureSpell,
    SpellLoS AS LoS,
    FROM SLAM.dbo."1415SLAMTAS";

    ReplyDelete
  7. Hey Matt
    I've just working with Qlik (and scripting in fact) so pardon the simplistic question.

    I need to set a starting date at a specific date/time. I want to store this in a variable so I can generate a master calendar based on today's date.

    Here's how I have defined the mindate variable:
    Set vMinDate='01/01/2016';

    In a temp cal table (with autogenerate, the std code), I do this
    date($(vMinDate)+RowNo()-1) as TempDate

    I can't seem to get it to recognise the string as a date.

    Thanks for any help!

    ReplyDelete
  8. But what if I have a text format - '2016 W01'. How can I convert it to a date format? I need 2 separate fields: Year and Week (in 'W01' format)

    ReplyDelete
  9. Hello,

    Please help in the below Query

    i have a field with [Actual 2013]
    i need to take Year from the above field

    Thanks in advance

    Regards
    Ali

    ReplyDelete
    Replies
    1. Hi Ali
      Try the right() function!

      Regards
      Matt

      Delete
  10. Hi,
    I have a timestamp string with the format '7/18/2017 8:11:59 AM', how do I convert it using the timestamp# function?
    Regards

    ReplyDelete
    Replies
    1. Try the format 'M/D/YYYY h:m:s TT'

      Regards
      Matt

      Delete