topic Formatting a date variable in the script? in QlikView

Formatting a date variable in the script?

Anonymous — Fri, 17 Mar 2017 09:19:36 GMT

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

Re: Formatting a date variable in the script?

Anil_Babu_Samineni — Fri, 17 Mar 2017 09:22:47 GMT

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;

Re: Formatting a date variable in the script?

Fri, 17 Mar 2017 09:23:35 GMT

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,

Re: Formatting a date variable in the script?

Fri, 17 Mar 2017 09:25:40 GMT

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.

Re: Formatting a date variable in the script?

Anonymous — Fri, 17 Mar 2017 09:26:25 GMT

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.

Re: Formatting a date variable in the script?

Fri, 17 Mar 2017 09:29:55 GMT

Hi,

You can try date(date#('date_string', 'YYYY-MM-DD'), 'DD\MM\YYYY') as date1

thanks