topic Is there a standardized date format I should implement on load? in QlikView

Is there a standardized date format I should implement on load?

Anonymous — Mon, 13 Jan 2014 20:55:58 GMT

I know QlikView does a great job of recognizing dates but on my QVD creation, should I transform each date so it is always recognized in further scripting without question.

I ask because I ran into an issue with an intervalmatch() solution I was trying and it did not seem to recognize the date properly.

So I ask, should we always transform the date, datetime and time fields in the load statement?

If so, what is recommended to always work?

Thanks

Re: Is there a standardized date format I should implement on load?

Colin-Albert — Mon, 13 Jan 2014 21:03:58 GMT

I load date fields with a date() function to ensure the date is held as a dual value.

You may also need to use date#() to ensure correct conversion depending on your date format.

E.g. Date(date#(date_field, date_format))

Date(date#(transaction_date, 'MM DD YY')) as trans_date

Re: Is there a standardized date format I should implement on load?

Nicole-Smith — Mon, 13 Jan 2014 21:04:19 GMT

In the beginning of your load script, you should see a list of variables that look like this:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

It is here where you can set the date format that you want to be recognized, so if you have one that is mostly used, set the date format here and QlikView should always recognize it as a date.

Re: Is there a standardized date format I should implement on load?

Colin-Albert — Mon, 13 Jan 2014 21:07:07 GMT

You can also set a default date format on a field by field basis on the document properties, number tab.

This screen also defines default number formats for each field.

Re: Is there a standardized date format I should implement on load?

Anonymous — Mon, 13 Jan 2014 22:08:44 GMT

Colin, that is what I was leaning towards and wondered if that would be correct.

Nicole, I am familiar with with initial date setup, it's just from one particular QVD I was loading it was bringing in the InvoiceDate column with two different datatypes. At least it was being read that way. This was in turn breaking my intervalmatch() which is why I wanted a good standard way to FORCE the data to bend to my will.

Thank you both for the quick response.

Re: Is there a standardized date format I should implement on load?

Mon, 13 Jan 2014 22:34:16 GMT

Hi Darrin, the Qlikview will store the dates and time stamps into numbers. So the the qlikview interpret the date fields easily without any problem.