4 Replies Latest reply: Mar 21, 2012 12:57 PM by Wanderson Marques RSS

    how convert text (January) to month (1)

      hi friends,

      how can I convert text "January" (February, ...) to month: 1 (2, ...) ?

       

      Detail info:
      in datasource is column "MyMonthName" (with values: January, February, March, ... ) and column "MyMonthID" (with related values: I, II, III, IV, ..., XII)
      Now I want after select of month "January" (in QlikView object ListBox with field "MyMonthName") and select of year (eg.2010) (in object Year) create text with date "1.1.2010"

        • how convert text (January) to month (1)
          Luca Cavallari

          Hello,

          if you dont wanna change your SET MonthNames format (which would automatically makes u able to compare the month "February" with number 2 for example) then you could use this:

           

          Month_Number:

           

          LOAD distinct

          RECNO() AS Month_Number,

          MyMonthName

          resident MyTable while RecNo()<=12;



           

          This way you can use Month_Number when u need to compare the month as text, having it associated with MyMonthName.

          • how convert text (January) to month (1)
            Jonathan Dienst

            Juraj

            The Month() function should do what you need. It returns a dual value, with a text representation of Jan, Feb, Mar... and a numeric value 1, 2, 3.If your data table also contains a Year field (like the script below), then you can select records for that month and year if you create listboxes for MyMonthName and MyYearName:

             

            DATATABLE:
            LOAD TranDate AS Date,
            Month(TranDate) AS MyMonthName,
            Year(TranDate) AS MyYearName,
            ....
            ;
            SQL SELECT
            TranDate,
            ...
            FROM ...
            ;


            If you must get the date as a value when the Month and Year are selected in listoxes, then I suggest that you create a variable like this in your script:

            Set vMyDate = MakeDate(myYearName, MyMonthName);

            Now vMyDate will contain 1.1.2010 when Jan and 2010 are selected. It will contain null if no month or no year is selected.

            Hope that helps

            Jonathan

            • how convert text (January) to month (1)
              Wanderson Marques

              Hi JurajChovan!

               

              For the text with the name of the month, is actually converted to a month value, try this:

               

              Date( Date# ( MyMonthName,'MMM'),'MMM')

               

              God bless you!

               

              Stive

              Brazil