Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Re: Date Conversion strange behaviour

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

sujeetsingh
Honored Contributor III

Re: Date Conversion strange behaviour

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

Not applicable

Re: Date Conversion strange behaviour

it is not a good practice to touch the excel data

Not applicable

Re: Date Conversion strange behaviour

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

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

H4/21/2013
H5/30/2013
H5/25/2013
I30/06/12
I31/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      

Not applicable

Re: Date Conversion strange behaviour

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

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

Not applicable

Re: Date Conversion strange behaviour

did u actually see what i'm using ?

there's nthing wrong in the syntax.

Regards

Not applicable

Re: Date Conversion strange behaviour

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

Highlighted

Re: Date Conversion strange behaviour

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

Community Browser