topic Re: RE:Date format in QlikView

RE:Date format

Chanty4u — Tue, 08 Dec 2015 08:58:34 GMT

hi all,

how to chnage the date formats while loading...

if i hve the data with diffrnt date formats?

thanks in advance''''

Suresh

Re: RE:Date format

tresB — Tue, 08 Dec 2015 09:00:17 GMT

Date(datefield, 'YourDesiredDateFormat') as NewDate

Re: RE:Date format

prieper — Tue, 08 Dec 2015 09:04:24 GMT

Do you mean that you will have different dateformats in the source-data?

If yes, you may use the ALT-funtion:

quote from OL:

alt( date#( dat , 'YYYY/MM/DD' ),

date#( dat , 'MM/DD/YYYY' ),

date#( dat , 'MM/DD/YY' ),

'No valid date' )

unquote

HTH Peter

Re: RE:Date format

raajaswin — Tue, 08 Dec 2015 09:04:50 GMT

Date(<DateColumn>,'Format') as NewDate

Format may be like MMDD, DDMM, DDMMM, DDMMMYYYY, DD/MM/YYYY ..etc...

Re: RE:Date format

effinty2112 — Tue, 08 Dec 2015 09:08:23 GMT

Hi,

Try this if the the date you want to change is currently a text field rather than a Date field with a dual representation:

Date(Date#('YourDateText','OldDateFormat'),'NewDateFormat')

Re: RE:Date format

Chanty4u — Tue, 08 Dec 2015 09:09:44 GMT

chk attached sample excel

Re: RE:Date format

jagan — Tue, 08 Dec 2015 09:10:27 GMT

Hi,

Try like this using Date()

Syntax:

Date(DateFieldName, 'DateFormat')

Example:

Date(BillDate, 'DD-MMM-YYYY')

Date(BillDate, 'MM/DD/YYYY')

Date(BillDate, 'DD/MM/YYYY') etc.

Hope this helps you.

Regards,

jagan.

Re: RE:Date format

Chanty4u — Tue, 08 Dec 2015 09:15:21 GMT

plz chk sample qvw

Re: RE:Date format

tresB — Tue, 08 Dec 2015 09:18:00 GMT

Try like:

Date(

Alt(

Date#(DateField, 'DD-MMM-YYYY') ,

Date#(DateField, 'DD-YYYY-MMM'),

Date#(DateField, 'MMM-DD-YYYY'),

Date#(DateField, 'MMM-YYYY-DD'),

Date#(DateField, 'YYYY-MMM-DD'),

Date#(DateField, 'YYYY-DD-MMM')

)

, 'YYYY-MMM-DD'

)

Edit: prieper‌ has already suggested similar. Corrected and included all posible date formats.

Re: RE:Date format

raajaswin — Tue, 08 Dec 2015 09:30:58 GMT

Check this file.. Hope it helps

Re: RE:Date format

jagan — Tue, 08 Dec 2015 09:42:25 GMT

Hi Suresh,

Try this script

Data:

LOAD

salesman,

city,

sales,

Date(Alt(Date#(years, 'DD-MMM-YYYY'), Date#(years, 'MMM-DD-YYYY'), Date#(years, 'YYYY-MMM-DD'), Date#(years, 'MMM-YYYY-DD'), Date(years))) AS Date,

Actualyears ;

LOAD salesman,

city,

sales,

Capitalize(years) AS years,

Capitalize(years) AS Actualyears

FROM

[sales.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: RE:Date format

Chanty4u — Tue, 08 Dec 2015 09:57:22 GMT

thnx ashwin....its exact wat am lukng.....thanx all for ur replies

Re: RE:Date format

Chanty4u — Tue, 08 Dec 2015 09:58:34 GMT

thnx peter