topic Re: date conversion from DD-MMM to MM/DD/YYYY in QlikView

date conversion from DD-MMM to MM/DD/YYYY

hosasahana — Tue, 10 Nov 2020 07:23:34 GMT

hi all,

I have an excel source which has date in this format as shown in below picture. After load qlik stores it as 20-Sep.I want to convert this to MM/DD/YYYY ()9/20/2020)format in qlik script. I tried date,date#,makedate etc... but nothing works. pl can someone help?

Re: date conversion from DD-MMM to MM/DD/YYYY

zhaofeng — Tue, 10 Nov 2020 08:01:27 GMT

Could you please provide your excel file.I think it may caused by data format

Re: date conversion from DD-MMM to MM/DD/YYYY

hosasahana — Tue, 10 Nov 2020 14:41:55 GMT

hi,

thanks for responding. I have attached a test file. Please check.

(I want to change this format in click script and not in excel.)

Re: date conversion from DD-MMM to MM/DD/YYYY

zhaofeng — Wed, 11 Nov 2020 08:19:06 GMT

I've read your attachment.I found the date format is different from your previous screenshot.I also got a screenshot.Whatever,I've made an example in the qvw file,please check it

Re: date conversion from DD-MMM to MM/DD/YYYY

hosasahana — Thu, 12 Nov 2020 05:41:13 GMT

hi,

I am unable to open the qvw locally as I do not have qlik in local machine since we work on cloud and currently without enterprise version cannot install as well I guess.

Please can you post the code line used directly if possible? sorry for the trouble.

Re: date conversion from DD-MMM to MM/DD/YYYY

QFabian — Thu, 12 Nov 2020 21:25:59 GMT

HI @hosasahana , this works fine for me :

LOAD A,
Month,
date("Month", 'MM/DD/YYYY ') as Date,
[Call Count],
D
FROM
test_dateconversion.xlsx
(ooxml, embedded labels, table is Sheet1);

Re: date conversion from DD-MMM to MM/DD/YYYY

QFabian — Thu, 12 Nov 2020 21:27:27 GMT

that works fine!

Re: date conversion from DD-MMM to MM/DD/YYYY

zhaofeng — Fri, 13 Nov 2020 03:15:15 GMT

I paste the source code below,please check it.

LOAD

A as User,
Month,
date(num(Month),'MM/DD/YY') as Year_Month_Day,
[Call Count] as Call_Count
FROM test_dateconversion.xlsx(ooxml, embedded labels, table is Sheet1);