Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Date format transformation

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

1 Solution

Accepted Solutions
Not applicable

Re: Date format transformation

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

7 Replies
Not applicable

Re: Date format transformation

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

Sokkorn
Honored Contributor

Re: Date format transformation

Hi Chanel,

Use this

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


Regards,

Sokkorn

Partner
Partner

Re: Date format transformation

Try :

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

Michael

Not applicable

Re: Date format transformation

Hi,

Use this

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

HTH

-Shruti

Not applicable

Re: Date format transformation

Hi, try the file i attached

Not applicable

Re: Date format transformation

HI

PFA..Hope this will helps you

Not applicable

Re: Date format transformation

Thanks for all your helpssss!!!