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: 
BootCam
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
Anonymous
Not applicable

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.

sunny_talwar

You can try this:

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

swuehl
MVP
MVP

Or maybe just

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

BootCam
Creator
Creator
Author

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

sunny_talwar

Awesome .

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

Best,

Sunny

Anonymous
Not applicable

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;