5 Replies Latest reply: Jul 13, 2013 9:24 AM by Rishabh Baj RSS

    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


        • Re: Splitting the field !!
          Kaushik Solanki

          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 !!

              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

              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 !!

                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