topic Re: Date string - split year and month in QlikView

Date string - split year and month

Sat, 14 Sep 2013 19:18:14 GMT

Hi everyone,

I have retail data (product registered) where registrations are formatted as 'YYYY-MM' (one date string).

I also have additional sales data (product shipped) where the format is seperated - 'YYYY' for Year and 'MM' for months.

When loading fields into my qlikview file, how can I turn my retail data from one string, to seperate out the Year and Month?

Thanks,

Paul

Re: Date string - split year and month

v_iyyappan — Sat, 14 Sep 2013 19:29:50 GMT

Hi,

When load the date filed in QVW

using Date(Date#(Datefield,'YYYY-MM')) as DateField

then u can use

Year(DateField) As Year,

Month(DateField) As Month

Date(DateField,'YYYY') As Year,

Date(DateField,'MM') As Month

Regards,

Re: Date string - split year and month

mdmukramali — Sat, 14 Sep 2013 19:35:02 GMT

Dear ,

you can use

Year(FieldName) as Year and Month(FieldName) as Month

or else

Right(FieldName,4) as Year,

Left(FieldName,2) as Month

Thanks,

Mukram.

Re: Date string - split year and month

MayilVahanan — Sat, 14 Sep 2013 20:18:27 GMT

Try like this

First Method:

Year(Date(DateField, 'YYYY-MM' )) as Year

Month(Date(DateField, 'YYYY-MM' )) as Month

Second Method:

Left(DateField,4) as Year,

Right(DateField,2) as Month

Third Method:

Subfield(DateField,'-',-1) as Month,

Subfield(DateField,'-',1) as Year

Regards,

R.Mayil Vahanan

Re: Date string - split year and month

CELAMBARASAN — Sat, 14 Sep 2013 21:28:32 GMT

If you need to make a date field then go with

Year(Date#(FieldName, 'YYYY-MM')) AS Year

Month(Date#(FieldName, 'YYYY-MM')) AS Month

If date field is not needed then you can use subfield

SubField(FieldName, '-', 1) As Year

SubField(FieldName, '-', 2) As Month