Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date Conversion Problem

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.

26 Replies
timanshu
Creator III
Creator III

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
Specialist
Specialist

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';

Not applicable
Author

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().

Not applicable
Author

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
Specialist
Specialist

Hi,

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

regards

Not applicable
Author

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

timanshu
Creator III
Creator III

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.