topic Upload excel date field with two format dates in qlikview in QlikView

Upload excel date field with two format dates in qlikview

Wed, 12 Dec 2012 11:29:25 GMT

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

Re: Upload excel date field with two format dates in qlikview

Gysbert_Wassenaar — Wed, 12 Dec 2012 12:08:55 GMT

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

Re: Upload excel date field with two format dates in qlikview

Wed, 12 Dec 2012 12:42:29 GMT

Thanks..

Re: Upload excel date field with two format dates in qlikview

Wed, 12 Dec 2012 12:42:37 GMT

Thanks..

Re: Upload excel date field with two format dates in qlikview

jonathandienst — Wed, 12 Dec 2012 12:58:07 GMT

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

Re: Upload excel date field with two format dates in qlikview

Mon, 17 Dec 2012 11:05:51 GMT

Thanks For help