topic Re: Date format transformation in QlikView

Date format transformation

Wed, 05 Feb 2014 08:25:03 GMT

Dear Community,

I have a field in general format , example:

20 Mar 2012

It is not in date format, so I need to convert this to date format, somehow my script failed to do so.

Appreciate if anyone could help!

Test:

Load

Left([Added On], 2) as TempDay,

TextBetween([Added On], ' ', ' ') as TempMonth,

mid([Added On], 😎 as TempYear

from xxx.xlsx

(ooxml, embedded labels, header is 1 lines, table is Table1);

TempDate:

Load *,

TempDay & '-' & TempMonth & '-' & TempYear as TempDate

Resident Test;

Date:

load *,

date(TempDate) as Date

Resident TempDate;

Drop table TempDate;

Thanks,

Chanel

Re: Date format transformation

Wed, 05 Feb 2014 08:31:34 GMT

Hi Chanel

Maybe you can interpret the string using built-it Date#() instead of manual parsing.

Try this: Date#([Added On], 'dd MMM YYYY')

Lukasz

Re: Date format transformation

Sokkorn — Wed, 05 Feb 2014 08:32:59 GMT

Hi Chanel,

Use this

Date(Date#([Added On],'dd MMM yyyy')) as TempDay,

Regards,

Sokkorn

Re: Date format transformation

agilos_mla — Wed, 05 Feb 2014 08:34:07 GMT

Try :

Load Date(Date#([Added On], 'D MMM YYYY'), 'M/D/YYYY')

Michael

Re: Date format transformation

Wed, 05 Feb 2014 08:36:17 GMT

Hi,

Use this

Date(date#([Added On],'DD-MMM-YYYY'),'DD-MMM-YYYY')

HTH

-Shruti

Re: Date format transformation

Wed, 05 Feb 2014 08:37:11 GMT

Hi, try the file i attached

Re: Date format transformation

Wed, 05 Feb 2014 08:37:45 GMT

PFA..Hope this will helps you

Re: Date format transformation

Wed, 05 Feb 2014 09:25:13 GMT

Thanks for all your helpssss!!!