topic Re: Splitting the field !! in QlikView

Splitting the field !!

Sat, 13 Jul 2013 11:59:24 GMT

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

Re: Splitting the field !!

kaushiknsolanki — Sat, 13 Jul 2013 12:09:01 GMT

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

Re: Splitting the field !!

Sat, 13 Jul 2013 12:26:18 GMT

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

Re: Splitting the field !!

Gysbert_Wassenaar — Sat, 13 Jul 2013 13:12:40 GMT

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

Re: Splitting the field !!

Sat, 13 Jul 2013 13:23:27 GMT

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

Re: Splitting the field !!

Sat, 13 Jul 2013 13:24:14 GMT

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