topic Re: Date Conversion strange behaviour in QlikView

Date Conversion strange behaviour

Tue, 07 May 2013 09:30:04 GMT

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

Re: Date Conversion strange behaviour

Tue, 07 May 2013 10:29:02 GMT

anyone?

Re: Date Conversion strange behaviour

sujeetsingh — Tue, 07 May 2013 10:57:53 GMT

Hi may be due to declaration default.not sure .

Plz send a sample file lets see thuis strange behave.

Re: Date Conversion strange behaviour

sujeetsingh — Tue, 07 May 2013 11:02:45 GMT

One thing when you are using conversion as

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

Re: Date Conversion strange behaviour

Tue, 07 May 2013 11:09:01 GMT

Hi,

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

the sample file is attached

Regards

Re: Date Conversion strange behaviour

sujeetsingh — Tue, 07 May 2013 11:16:50 GMT

I am talking about sample qvw..

Re: Date Conversion strange behaviour

Tue, 07 May 2013 11:23:12 GMT

pls see attached

Re: Date Conversion strange behaviour

Tue, 07 May 2013 11:45:45 GMT

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.

Re: Date Conversion strange behaviour

sujeetsingh — Tue, 07 May 2013 11:48:20 GMT

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"

Re: Date Conversion strange behaviour

Tue, 07 May 2013 11:51:29 GMT

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.

Re: Date Conversion strange behaviour

kaushiknsolanki — Tue, 07 May 2013 11:53:24 GMT

Hi,

You must understand the working of Date#() function. Let me explain in detail.

Date#(Field,Format) :

This function is used to convert the text into Date. So suppose if I have a text 21/01/2012 then I can use the Date#() to convert this text into date format.

Parameters:

Field:

This is the name of the Text field which is coming from any of your data source.

Format:

This is the format in which the text contains the date.

Example

Text : 21-03-2013

Expression will be : Date#(Text,'DD-MM-YYYY')

Text : 2013/03/19

Expression will be : Date#(Text,'YYYY/MM/DD')

Note : The format should be the same format in which your text is.

Regards,

Kaushik Solanki

Re: Date Conversion strange behaviour

sujeetsingh — Tue, 07 May 2013 11:59:51 GMT

It seems same viswarajan...........there is two data types in a column!!1

Re: Date Conversion strange behaviour

Tue, 07 May 2013 12:15:26 GMT

it is not a good practice to touch the excel data

Re: Date Conversion strange behaviour

Tue, 07 May 2013 12:16:53 GMT

Hi,

i completely know the date# working, you just need to tell me where the expression is wrong.

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

why ?

Regards

Re: Date Conversion strange behaviour

kaushiknsolanki — Tue, 07 May 2013 12:22:49 GMT

HI,

This is because the field which is coming from your excel file contains the Data in Date as well as text format.

If you look at the excel file you will find the data

H	4/21/2013
H	5/30/2013
H	5/25/2013
I	30/06/12
I	31/01/13
I	31/01/13

You can see here that the value is in Date format for H but for I it is not.

Regards,

Kaushik Solanki

Re: Date Conversion strange behaviour

Tue, 07 May 2013 12:27:50 GMT

Yes, i know, but how does the removal of "date#([date] , 'YYYY/MM/DD' )": from alt solves the problem.

this is is just one of the format which was present in the earlier version of this file but not now in this file.

In future sone text in the format YYYY/MM/DD can appear again so i will have to use this which i believe will work then.

But the alt function is not concerned if the format is NOT present in the file, it is concerned IF IT IS present then it converts to the desired default/set format else if none of the mentioned format is present then it will return date(0) i.e. 30/12/1899.

Again, why do we have to remove "date#([date] , 'YYYY/MM/DD' )" from the alt to make it work ?

Regards

Re: Date Conversion strange behaviour

kaushiknsolanki — Tue, 07 May 2013 12:35:34 GMT

HI,

I am not sure why it is behaving like that, but try by putting that statement at the end i.e before num#() function in your expression and see how it goes.

Regards,

Kaushik Solanki

Re: Date Conversion strange behaviour

Tue, 07 May 2013 15:26:46 GMT

did u actually see what i'm using ?

there's nthing wrong in the syntax.

Regards

Re: Date Conversion strange behaviour

Tue, 07 May 2013 15:30:11 GMT

Hi Kaushik,

If you're nt sure why this is happening then i think its quite a problem as i rate you very high,

I can try putting it at the end and it might work but that won't give a logic,why did it work ?

There is something which we're missing or might be a bug.

Regards

Re: Date Conversion strange behaviour

rwunderlich — Wed, 08 May 2013 14:05:17 GMT

The 'YYYY/MM/DD' does match your "I" values.

30/06/12

The date parsing rules allow YYYY to be a 2 digit year as well. So "30" is interpreted as "2030".

I think you can resolve the problem by moving the "YYYY/MM/DD" to the end of the list. Then your I values should get picked up by the "DD/MM/YYYY" format which is earlier in the list.

-Rob

http://robwunderlich.com