Solved: Re: Date Conversion strange behaviour - Qlik Community

Report Inappropriate Content · ‎2013-05-07

Hi,

I'm using the script below to convert the dates in the attached excel sheet.

LOAD Name,

date(alt(date#([date] , 'YYYY/MM/DD' ),date#([date], 'MM/DD/YYYY' ),date#( [date] , 'MM/DD/YY' ),date#([date], 'DD/MM/YYYY' ),

date#([date], 'MM/DD/YYYY'),date#([date], 'M-DD-YY' ),Date#([date], 'DD/MM/YY'),

date#([date], 'MM-DD-YY' ), num#([date], 'M-DD-YY' ),0)) as date

FROM

test.xlsx

(ooxml, embedded labels, table is Sheet1);

Please note that although the format 'YYYY/MM/DD' is not present in the excel sheet but still i'm using it as this format used to be there in an earleir version of the file & could be there in future also.

The problem is the conversion is correct for every name exect for name: I for which I get :

13/02/2028

13/04/2030

12/06/2030

13/01/2031

13/03/2031

My set/default date format in the file is DD/MM/YYYY.

When i remove date#([date] , 'YYYY/MM/DD' ) from the alt then it works fine.

Why is this happening ??

Regards

swuehl · ‎2013-05-08

Given your two formats in your example, 12/06/30 is a valid value in both date formats. So there is an ambiguity if you want to get the 'true' date.

It's always a good thing if your input data is in a well defined format, so if you can get your users to use one format, that would be much better than guessing around when reading in, right?

If possible, use a four digit year format. QV will use some AI to determine the full four digit year (e.g. determine 2013 from 13). I believe it will interprete 63 - 99 as being in 20th century, and 0 to 62 in 21st century (range is +-50 years from today).

Maybe this is according to what your users expect, maybe not.

And finally, there is an ISO standard for dates, which avoids the DD/MM/YY(YY) resp. MM/DD/YY(YY) etc. ambiguity altogether: 'YYYY-MM-DD'

View solution in original post

Report Inappropriate Content · ‎2013-05-07

anyone?

sujeetsingh · ‎2013-05-07

Hi may be due to declaration default.not sure .

Plz send a sample file lets see thuis strange behave.

sujeetsingh · ‎2013-05-07

One thing when you are using conversion as

Date#(Date,yyyy/mm/dd) tey using Date() function inspite of Date#()

Report Inappropriate Content · ‎2013-05-07

Hi,

what do u mean by "Date() function inspite of Date#()"

the sample file is attached

Regards

sujeetsingh · ‎2013-05-07

I am talking about sample qvw..

Report Inappropriate Content · ‎2013-05-07

pls see attached

Report Inappropriate Content · ‎2013-05-07

hi

syntax for changing the format of date.

Date(Date#(fieldname,'DD-MM-YYYYY'),'YYYY-MM-DD')

HERE- within date#() function we write the format of your field in which date present.

and within date() function we write the format of date which i want.

so firstly use correct syntax.

sujeetsingh · ‎2013-05-07

Please explain why are you using this legthy conversion

date(alt(date#([date] , 'YYYY/MM/DD' ),date#([date], 'MM/DD/YYYY' ),date#( [date] , 'MM/DD/YY' ),date#([date], 'DD/MM/YYYY' ),

date#([date], 'MM/DD/YYYY'),date#([date], 'M-DD-YY' ),Date#([date], 'DD/MM/YY'),

date#([date], 'MM-DD-YY' ), num#([date], 'M-DD-YY' ),0)) as date

what you want the date should look like "yyyy/mm/dd"

Report Inappropriate Content · ‎2013-05-07

bydefault Excel has a format of date.

but in your excel some date written according to format, but some date take as a text not in date format.

so firstlt correct your excel data according to date format.