Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Month Number to Month Name conversion

Hi,

I have a Month field in my Table. And that Field contains values Like this = 'JANUARY', 'FEBRUARY' Like this format. In some of my calculation I am using Maximum Month. So I create a variable vMaxMonth = max(Month).

To Make the Month as Number values I used this piece of code in the script:

LOAD

if(capitalize(MONTH) = 'January', 1,

      if(capitalize(MONTH) = 'February', 2,

      if(capitalize(MONTH) = 'March', 3,

    if(capitalize(MONTH) = 'April', 4,

      if(capitalize(MONTH) = 'May', 5,

      if(capitalize(MONTH) = 'June', 6,

      if(capitalize(MONTH) = 'July', 7,

      if(capitalize(MONTH) = 'August', 8,

      if(capitalize(MONTH) = 'September', 9,

     if(capitalize(MONTH) = 'October', 10,

      if(capitalize(MONTH) = 'November', 11, 12))))))))))) as [Month],

But when I am showing month in the list box I have to show the Month Like this: Jan, Feb, Mar, Apr Like this.

I was also trying to use the INLINE to Make the Month ordered like this:

LOAD * INLINE [

    Month

    Jan

    Feb

    Mar

    Apr

    May

    Jun

    Jul

    Aug

    Sep

    Oct

    Nov

    Dec

];

But since the Month field is getting number value with the using of IF statement, INLINE month can not match with that.

Can anybody guide me how can i achieve above mentioned functionality from the script?

Thanks,

Sk

6 Replies
Highlighted

This will return the month in Dual format so in text format it will display the Short Month Name and in numeric format can be sorted.

     =Date(MakeDate(2001, Month(Today())),'MMM')

You can substitute Today() with your actual date field.

Highlighted

You can try this:

Month(MakeDate(Year, Date#(Capitalize(MONTH), 'MMMM'))) as Month

Highlighted
MVP
MVP

Or maybe just

Month( Date#( Capitalize(MONTH),'MMMM')) as Month

Highlighted
Creator
Creator

Hi All,

I got a solution of this problem by Gysbert Wassenar in another forum in Qlik community. But thank you all for providing a solution for my problem.

Thanks a lot!

Cheers,

Sk

Highlighted

Awesome .

Please close this thread by marking it 'Assumed Answer' and mark for helpful answers, if any

Best,

Sunny

Highlighted
Contributor III
Contributor III

Hi,

Here is the work around for Month_Name.

Master_Calendar:

LOAD Date_Tmp as Event_Dates,

       Week(Date_Tmp) as Week,

       Year(Date_Tmp) as Year,

       Capitalize(Month(Date_Tmp)) as Month,

       Day(Date_Tmp) as Day,

       WeekDay(Date_Tmp) as DayWeek

RESIDENT TmpCalendar;

INNER JOIN

Month_Tmp: //to ensure a correct "sort by load order"

LOAD * INLINE [

   Month, Month_Name

    1,Jan

    2,Feb

    3,Mar

    4,Apr

    5,May

    6,Jun

    7,Jul

    8,Aug

    9,Sep

    10,Oct

    11,Nov

    12,Dec

];

DROP Table TmpCalendar;