Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have an application where I have made a pivot table. The user can export the data to Excel.
The date variable is shown like this in the table: DD-MM-YYYY, but a user have reported to me, that when she export the data to her Excel, the variable is shown like this: MM-DD-YYYY.
I have looked in properties in the Numbers tab, but there I can not see my variable/dimension. Only my expressions.
Is it possible to define the dateformat in the script, where I load in the data?
The data is loaded like this in Qlikview:
ODBC CONNECT TO SASWB;
Bedoem:
SQL SELECT *
FROM path.dataset;
And when I load in the data in a testversion, it is loaded in via Excel:
LOAD var1,
var2,
var3,
var4,
date,
var6,
var7;
FROM
[path\dataset.xlsx]
(ooxml, embedded labels, table is dataset);
Can I write something like:
LOAD var1,
var2,
var3,
var4,
date format 'DD-MM-YYYY' ,
var6,
var7;
FROM
[path\dataset.xlsx]
(ooxml, embedded labels, table is dataset);
And how can I make this formatting when I read the data like this?:
Bedoem:
SQL SELECT *
FROM path.dataset;
Kind regards from Maria
You may write like below
LOAD var1,
var2,
var3,
var4,
Date(date,'DD-MM-YYYY') as date, // I assume you have different date format not the string
var6,
var7;
FROM
[path\dataset.xlsx]
(ooxml, embedded labels, table is dataset);
SQL SELECT *
FROM path.dataset;
Hi,
go to script (Ctrl+E) in the top of the page you can see
SET DateFormat='M/D/YYYY';
edit that as
SET DateFormat='DD/MM/YYYY';
I hope it helps
thanks,
I have faced similar issue , but the below code solved my problem ,please try
Date(Date#([Field Name],'DD/MM/YYYY'),'DD-MMM-YYYY') as [Field Name],
Note: [Filed Name] is the column name of your application and it s format is DD/MM/YYYY
Let me know if it solves.
It allready says;
SET DateFormat='DD-MM-YYYY';
I guess Qlikview don't know which variables are date values even though it is a datevariable in SAS.
Hi,
You can try date(date#('date_string', 'YYYY-MM-DD'), 'DD\MM\YYYY') as date1
thanks