Date Conversion Problem

[#1]

Hi All,  I am facing a problem with the date conversion.

I have this excel file in which i have a date column in which i intend to have dates in 'DD/MM/YYYY' format.

But when i import this file into qlikview it appears in 'MM/DD/YYYY'.

Converting these dates using the :

date(date#(date,'DD/MM/YYYY'),'DD/MM/YYYY') as date lends me null values in the frontend.

I also tried using date(makedate(subfield(date,'/',3),subfield(date,'/',2),subfield(date,'/',1)),'DD/MM/YYYY')

but still i am getting null values.  I am not able to fetch dates correctly to the application. I think its a problem of excel file, but i am not able to identify it correctly and solve it.

Any help would be appreciated. 

Thanks in advance. 

P.S. : Attaching the excel file which contains date column.

[timanshu]

Hi Shashi,

Actually what's happening is : Date column in excel is in Date format. And Excel interprets Date format as 'MM/DD/YYYY'. So when we load that excel in qlikview, it takes the date in same format. As an example, take this expression.  Date(date, 'DD/MM/YYYY') with date =1/8/2015  , then it interprets date as 8 jan 2015 but as you have defined output format it will show 8/1/2015. For this you can change format of date column in excel like Custom etc. Because  you mean to define date in some other format and that is not the date format for excel.

[tyagishaila]

Hi Shashi,

You can see following script on editor(ctrl+E) window.

change DateFormat here.

Hope it will work.

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

[#4]

Hi Timanshu,

I did try the custom option but then too the same problem was arising.

SO i just created a date required by making use of makedate().

[#5 · Accepted solution]

Hi Nadeem,

My script was as follows:

//Just to check whether the month is being detected correctly

Load *,

month(newdate) as M;

//Creation of dates required from the exisiting dates

Load*,

month(date) as month,

date(makedate(SubField(date,'/',3),SubField(date,'/',1),SubField(date,'/',2)),'DD/MM/YYYY') as newdate;

//Loading date from the excel file

LOAD

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

FROM

doubt.xlsx

(ooxml, embedded labels, table is Sheet3);

[fashid]

Hi,

Thanks shashi but why have u used your delimeter as '/'  in subfield function when in excel source you have it as '-'.

regards

[#7]

sorry i had changed the excel file later...sorry for the confusion

[timanshu]

Hi shashi,

Custom Dates work.Kindly check attached File and QVW.

I have taken output date in qv app as 'DD-MMM-YYYY', you can give your own.