Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

BootCam
Contributor

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

Tags (1)
6 Replies

Re: Month Number to Month Name conversion

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.

Re: Month Number to Month Name conversion

You can try this:

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

MVP
MVP

Re: Month Number to Month Name conversion

Or maybe just

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

BootCam
Contributor

Re: Month Number to Month Name conversion

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

Re: Month Number to Month Name conversion

Awesome .

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

Best,

Sunny

meet_purushu
New Contributor III

Re: Month Number to Month Name conversion

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;

Community Browser