Upload excel date field with two format dates in qlikview

[#1]

I have a excel file to upload in qlikview. this file contains a date field which has dates in below two formats:

mm/dd/yyyy

mmm yyyy

while uploading data in qlikview all dates are converted in mm/dd/yyyy format.

Ex:

excel date field

12/1/2012

12/2/2012

12/3/2012

dec 2012

qlikview date field:

12/1/2012

12/2/2012

12/3/2012

12/1/2012

Is there any way to diffrentiate between these two in qlikview??

[Gysbert_Wassenaar]

Nope, Qlikview doesn't (can't) read the date format from excel. It reads the number values of the dates and applies the date format you specified (or system default if you didn't).

[#3]

Thanks..

[#4]

Thanks..

[jonathandienst]

Hi

You can use the Alt function to read a date which can be in more than one format. Use it like this in your load script:

     Alt(Date#(DateField, 'mm/dd/yyyy'), Date#(DateField, 'mmm yyy')) As Date

Invalid date formats (ie trying to read a MMM YYYY date in the first clause) will return null.

Alt will evaluate the expressions in turn until one returns a valid number (and dates are really just special numbers). You can have any number of expressions in an Alt function.

Hope that helps

Jonathan

[#6]

Thanks For help