Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

QlikView not recognizing dates in Excel sheet

Hi Community,

I have some Dates values in the Excel which QlikView is not able to read. While importing itself, it is not showing any values fetched from Excel sheet.Please see below snapshot:

error loading image

I don't want to make any changes in the Excel Format. Is there anything we can do from QlikView side so it is able to read values from excel.

many thanks in advance!

Regards,

Sharma

1 Solution

Accepted Solutions
Highlighted
Creator III
Creator III

Hi Sharma,

Although its showing number format while loading the date field from excel sheet but it will treat it as Date only after loading

Else you can use The Date( ) function to the field that is present in Formatting Function type while loading..

I am sending you the attatched document of sucess fully load of the date value..

Thanks

Sunil

View solution in original post

9 Replies
Highlighted
Not applicable

Hi Sharma,

I tried in QlikView 9.0 SR 5. it is working here.

Highlighted
Creator III
Creator III

Hi Sharma,

Although its showing number format while loading the date field from excel sheet but it will treat it as Date only after loading

Else you can use The Date( ) function to the field that is present in Formatting Function type while loading..

I am sending you the attatched document of sucess fully load of the date value..

Thanks

Sunil

Highlighted
Creator III
Creator III

Hi Sharma,

Although its showing number format while loading the date field from excel sheet but it will treat it as Date only after loading

Else you can use The Date( ) function to the field that is present in Formatting Function type while loading..

I am sending you the attatched document of sucess fully load of the date value..

Thanks

Sunil

View solution in original post

Highlighted
Specialist
Specialist

@Raj: I am usinmg 8.5 only. Anyway thanks for your concern.

@Sunil: I have already tried that but it is not helping me. Even I tried the code you have given me but still not able to achieve it. I am using QV 8.5.

is anything to do with the settings?

Thanks!

Highlighted
Creator III
Creator III

HI Sharma,

I think you have problem in the excel sheet.. the field containing the Date value is not recognizing by the Qlikview.

I have also faced with the same problem and then copied the Excelsheet to another sheet and formatted the date field with correct date field then it was loaded.

And also you can set the Date Format in the Edit script by default it comes as 'm/d/yyyy' you can also set it like that..

But my thought is you have the problem with your 'Microsoft excel sheet'

Highlighted
Not applicable

You can try with upgrading this to QV 9.0. As there might be some bugs in 8.5 version. There is nothing wrong in your excel sheet. It depends on the interface (ETL Part) which is not allowing it to read data from that format.

Regards,

Raj Kishor

Highlighted
Specialist
Specialist

Hi Sunil,

I don't want to do any changes in my excel sheet as it is provided by client and I do't have access to change anything in this. I am just trying to pull the data from this but its not working out. Is there something we can do without doing the changes in the Excel sheet ?

Any help is much appreciated.

Highlighted
Creator III
Creator III

HI Sharma,

I am not telling you to change any thing in the data value in the Excel sheet.

I just want to tell you change in the settings of your 'Microsoft Excel Sheet'.

There you need to set the format as timestamp as 'mm/dd/yyyy hh:mm:ss'

You can try it out.

Why i am telling you Bcoz the .xlsx file what you have posted is working fine with me..

but at your end it's not..

Highlighted
Specialist
Specialist

Hi Sunil/Raj,

I figured out the problem. QV was not able to read it from Excel 2007 but it is able to read from Excel 2003. So now I have changed my Excel to 2003 and is working all fine. Thanks for your suggestions. Yes