topic Re: Month Number to Month Name conversion in QlikView

Month Number to Month Name conversion

BootCam — Thu, 03 Mar 2016 06:58:17 GMT

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,

Re: Month Number to Month Name conversion

Anonymous — Thu, 03 Mar 2016 17:31:29 GMT

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

sunny_talwar — Thu, 03 Mar 2016 18:05:18 GMT

You can try this:

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

Re: Month Number to Month Name conversion

swuehl — Thu, 03 Mar 2016 18:28:25 GMT

Or maybe just

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

Re: Month Number to Month Name conversion

BootCam — Sun, 06 Mar 2016 23:59:06 GMT

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,

Re: Month Number to Month Name conversion

sunny_talwar — Mon, 07 Mar 2016 09:35:58 GMT

Awesome .

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

Best,

Sunny

Re: Month Number to Month Name conversion

Anonymous — Thu, 19 May 2016 02:30:52 GMT

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;