Splitting the field !!

[#1]

Hello Experts,

  I have a field called Year month  - this is in the format 201301 – which is nothing but the year and month . this needs to be changed to

(Month Jan // Year 2013 as two separate). It should get display as Jan-13.

How can we do this !!

I have loaded and done

    Year([Year Month]) as Year,

    Month([Year Month]) as Month

But the results comes in some number format. (Ex:204751)

Once it is done i need to combine this with another Date field, may be as a Key but am not sure yet.

Kindly provide with your answers, your help will be appreciated.

Thanks

Paul

[kaushiknsolanki]

Hi,

     Try this.

     Load Monthname(Makedate(Left(YearMonth,4),Right(YearMonth,2))) as MonthYear

     ,Date(Date#(YearMonth,'YYYYMM'),'MMM-YY') as MonthYear1 inline [

     YearMonth

     201301

     201302

     201303

     ];

Regards,

Kaushik Solanki

[#3]

Hi Kaushik,

Thanks for the response. I did not understand why Inline should get load.

What is the purpose of this, without inline also it works.

secondly does qlikview understant Left and Right like SQL... I was not knowing these functions are available.

Thanks

[Gysbert_Wassenaar]

Try:

     date(date#([Year Month],'YYYYMM'),'MMM-YY') as [Month Year]

The dates in that [Month Year] field will have a numeric value that's the first day of the month and a text value for display in the MMM-YY format. Because of the numeric value it can easily be used for calculations, for examply using the addmonths or addyears functions.

If you want to split the field in a month and a year field try this:

    Year(date#([Year Month],'YYYYMM')) as Year,

    Month(date#([Year Month],'YYYYMM')) as Month

[#5]

you can try using the following in your script:

ABC:

left([Year month], 4) as Year

monthname(right([Year month], 2)) as Month

monthname(right([Year month], 2)) &' - '& left([Year month], 4) as Month_of_Year

[#6]

you can try using the following in your script:

ABC:

left([Year month], 4) as Year

monthname(right([Year month], 2)) as Month

monthname(right([Year month], 2)) &' - '& left([Year month], 4) as Month_of_Year