topic Re: Date Formats in QlikView

Date Formats

danielnevitt — Wed, 28 May 2014 11:35:42 GMT

Hi,

I would really appreciate some help with standardising date formats from two different data sources, as part of my load script.

Format

Field1 dd/mm/yyyy hh:mm:ss

Field2 yyyymm

I would ideally like both formats to be changed to mmm-yy, but I am not sure how to write this as part of the load script.

Thanks in advance for any help.

Regards,

Daniel

Re: Date Formats

Anonymous — Wed, 28 May 2014 11:36:38 GMT

date ( Field1 , 'MMM-YY' )

Re: Date Formats

its_anandrjs — Wed, 28 May 2014 11:38:52 GMT

Try to write like

Load

Date(Field1, 'MMM-YY') as Field1

Date(Field2, 'MMM-YY') as Field2

From Location;

Load

Date(Date#(Field1, 'dd/mm/yyyy hh:mm:ss'),'MMM-YY') as Field1

Date(Date#(Field2, 'yyyyymm'), 'MMM-YY') as Field2

From Location;

Re: Date Formats

senpradip007 — Wed, 28 May 2014 11:39:31 GMT

For Field1

Date(Field, 'MMM-YY')

For Field2

Date(Date#(Field2, 'YYYYMM'), 'MMM-YY')

Re: Date Formats

tresB — Wed, 28 May 2014 11:41:39 GMT

Load

Date(Floor(Field1), 'MMM-YY') as Field1

Date(Date#(Field2, 'YYYYMM') 'MMM-YY') as Field2

From <>;

Assuming Field1 is being read as a date field already, otherwise parsing function date#() has to be used there too.

Re: Date Formats

Anonymous — Wed, 28 May 2014 11:42:47 GMT

use the 'replace' function
ex:

u r date is like 20-03-2014

you want show like 20/03/2014

Date(replace(filedname,'-','/'),'DD/MM/YYYY')

case2:

Directly

Date(filedname,'DD/MM/YYYY') use this code for all the dates in the scripy

Re: Date Formats

danielnevitt — Wed, 28 May 2014 12:35:59 GMT

Hi,

Thank you for all the replies.

I used the function Date(Date#(Field2, 'yyyyymm'), 'MMM-YY') as Field2

Regards,

Daniel