July 13, 2014

Time and Date Confusion 2



Date conversion or interpretation functions

 Qlikview is able to automatically interpret dates that match te format set in your PC locale, or in the the Qlikview environment variables (usually automatically added to the start of the script in a new document)

Set TimeFormat='hh:mm:ss';
Set DateFormat='YYYY/MM/DD';
Set TimestampFormat='YYYY/MM/DD hh:mm:ss[.fff]';

However, the date and time values in your source systems are frequently in a different format. This is where the date interpretation functions come in:

  • Date#(stringvalue[, format])
  • Time#(stringvalue[, format])
  • Timestamp#(stringvalue[, format])
  • Interval#(stringvalue[, format]) 

The functions ensure that the date field is correctly interpreted. So lets assume that the field is in YYYYMMDD format (eg in a load statement):

    Date#(SourceField, ‘YYYYMMDD’) As DateField,

The interpretation functions only affect the numeric value of the dual. The above expression will return the correct numeric value, but the text format will still be YYYYMMDD format, as this what Qlikview inferred as the correct format from the first record. If you would like to corrrectly interpret the date and format it on the default format for you environment/locale, them combine the interpretation function with the relevamt formatting function, like this:

     Date(Date#(‘YYYYMMDD’)) As DateField,
     Date(Date#(‘YYYYMMDD’), ‘D MMM YYYY’) As DateField2,  

The first will produce a date formatted in the default date format and the second in D MMM YYYY format.
Hopefully this post and the previous one helps you to  understand how to use the date/time format and interpretation functions.

4 comments:

  1. Hello Jonathan,

    We have developed a new gen developer friendly BI framework with some extremely unique features. Would like to give you early access & love to hear your opinion. Please do let me know of how to reach out to you. Would be launching product in 1 week from now.

    Also could you please share your email details for further communication.

    Regards,
    Anugraha

    ReplyDelete
  2. Your providing such a valuabe information about studying..and also have some good key

    points to every student.
    Qlikview Interview

    Questions | Qikview Training Videos | Qlikview online Training Hyderabad

    ReplyDelete