Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have this issue with date formatting which I'd like some insight.
I have date fields coming from a QVD and when it is loaded in a specific manner, the formatting I specify is ignored. In the example below, the fields ACTIVATION_DT and TERMINATION_DT is saved in 'DD/MM/YYYY' format in the QVD.
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='YYYY-MM-DD';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
FACT_TABLE_TEMP:
LOAD
ACTIVATION_DT,
TERMINATION_DT
FROM [lib://data/xxx.qvd]
(qvd);
FACT_TABLE:
NOCONCATENATE
LOAD
DATE(ACTIVATION_DT, 'YYYY-MM-DD') AS ACTIVATION_DT,
DATE(TERMINATION_DT, 'YYYY-MM-DD') AS TERMINATION_DT
RESIDENT FACT_TABLE_TEMP;
DROP TABLE FACT_TABLE_TEMP;
When viewing the fields, the fields ACTIVATION_DT and TERMINATION_DT is still shown as 'DD/MM/YYYY' and not 'YYYY-MM-DD' as specified in the load script. Renaming the fields to another name works, but I would like to use the original names of those fields. What is the issue about here?
Result of the load below:
Thank you in advance!
But it is still getting read as date? To check this you can check if the date is left or right oriented in the list box object. If it is right oriented, then it is getting lead correctly by QlikView, otherwise its not. Look here for more details on how to read dates in QlikView
Now if it is read properly, you can change its format (for everywhere in the application) by going to the document properties and give it a new format. This format will then be used everywhere in the application, regardless of whatever format you provide in the script (Have a look at the attached application)
Script
SET DateFormat='M/D/YYYY';
Table:
LOAD Date(Today(), 'DD/MM/YYYY') as Date
AutoGenerate 1;
Hi Sunny T,
I'm using QlikSense, and the dates are on the right side as you have mentioned, but I don't think there is a global formatting setting that can be applied in QlikSense.
Wong