topic Re: Date Converter in QlikView

Date Converter

Wed, 25 Sep 2013 10:23:27 GMT

Hello,

Please i have a field date which contain dates in the format friday,13 March 2013

i want to convert this field to 13/3/2013

how to do that?

Anonymous — Wed, 25 Sep 2013 10:29:53 GMT

Use date#() function:

date(date#(YourField, 'WWWW, DD MMMM YYYY'),'DD/M/YYYY')

Regards,

Michael

tresB — Wed, 25 Sep 2013 10:34:29 GMT

Try like :

Load

Date(MakeDate(Right(YourField,4), Num(Month(Date#(Mid(YourField,11,3),'MMM'))) , Mid(YourField,8,2)) , 'DD/MM/YYYY') as NewDate

From <>;

Edit : Corrected the character positioning and string function

Wed, 25 Sep 2013 11:24:11 GMT

Thanks tresesco and michael

but the problem is the original date field is in mdx query and i load it to qlikview correctly ok

when i try to add additional field to the load script of the mdx it give me the new date field empty

how can i do it? to add additional field to loaded data from mdx query generator

tresB — Wed, 25 Sep 2013 11:29:17 GMT

Try like this:

Load

Date(MakeDate(Right(YourField,4), Num(Month(Date#(Mid(YourField,11,3),'MMM'))) , Mid(YourField,8,2)) , 'DD/MM/YYYY') as NewDate,

OtherField1,

OtherField2 ;

SQL Select

YourField, // assuming this is your date field in the DB

OtherField1,

OtherField2

From <Your orginal DB>;

Note: The new date field can't be done under SQL statement. It has to be under LOAD.