topic Re: Date format issues in load script in QlikView

Date format issues in load script

Anonymous — Mon, 30 Mar 2015 11:45:14 GMT

I have a spreadsheet with the date data as 3/16/2015 (to be read M/DD/YYYY)

How can I format this in the load script to load in as DD/MM/YYYY so each month will be a double digit number?

Anonymous — Mon, 30 Mar 2015 11:48:44 GMT

change the script:

Date( YourTimeDimension, 'DD/MM/YYYY') as YourTimeDimension,

Gysbert_Wassenaar — Mon, 30 Mar 2015 11:49:16 GMT

Excel dates are often already numeric values with date formatting. In that case you only need the date() function:

LOAD

date(MyExcelDate, 'DD/MM/YYYY') as MyNewDate

FROM ....

But if the excel values are string values then you also need the date#() function:

LOAD

date(date#(MyExcelDate, 'M/DD/YYYY'),'DD/MM/YYYY') as MyNewDate

FROM ....

MK_QSL — Mon, 30 Mar 2015 11:52:15 GMT

Date(Date#(DateField,'M/DD/YYYY'),'DD/MM/YYYY') as DateField

avinashelite — Mon, 30 Mar 2015 11:54:55 GMT

Refer to this document for the date formatting

sunny_talwar — Mon, 30 Mar 2015 11:56:29 GMT

gwassenaar does the order of Date#(Date(...)) matter? I feel that shouldn't the second MyNewDate be like this?

LOAD

Date(Date#(MyExcelDate, 'M/DD/YYYY'),'DD/MM/YYYY') as MyNewDate

FROM ....

Let me know if it doesn't matter.

Best,

Sunny

Anonymous — Mon, 30 Mar 2015 12:03:58 GMT

Many thanks!! Date(Date#.......worked like a charm!!

Gysbert_Wassenaar — Mon, 30 Mar 2015 16:43:13 GMT

Yes, you're totally right. Thanks for pointing out my error.

sunny_talwar — Mon, 30 Mar 2015 17:09:55 GMT

That was more sort of a question than to point out your error. But I am glad it served both the purposes

Best,

Sunny