Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

Date Conversion strange behaviour

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Date Conversion strange behaviour

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'

25 Replies
Not applicable

Re: Date Conversion strange behaviour

anyone?

sujeetsingh
Honored Contributor III

Re: Date Conversion strange behaviour

Hi may be due to declaration default.not sure .

Plz send a sample file lets see thuis strange behave.

sujeetsingh
Honored Contributor III

Re: Date Conversion strange behaviour

One thing when you are using conversion as

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

Not applicable

Re: Date Conversion strange behaviour

Hi,

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

the sample file is attached

Regards

sujeetsingh
Honored Contributor III

Re: Date Conversion strange behaviour

I am talking about sample qvw..

Not applicable

Re: Date Conversion strange behaviour

pls see attached

Not applicable

Re: Date Conversion strange behaviour

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
Honored Contributor III

Re: Date Conversion strange behaviour

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"

Not applicable

Re: Date Conversion strange behaviour

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.