Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Splitting the field !!

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


5 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

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


talk is cheap, supply exceeds demand
Not applicable
Author

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

Not applicable
Author

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