Month Number to Month Name Tricky one

[BootCam]

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

[Gysbert_Wassenaar · Accepted solution]

Try this:

mapMonths:

MAPPING LOAD Month,Number as MonthNum INLINE [

Month, Number

January, 1,

February, 2,

March, 3,

April, 4,

May, 5,

June, 6,

July, 7,

August, 8,

September, 9,

October, 10,

November, 11

December, 12

];

Data:

LOAD

     dual(Capitalize(MONTH),ApplyMap('mapMonths',Capitalize(MONTH))) as [Sales Month],

     ...other fields,

FROM

     ...

     ;

[BootCam]

Hi Gysbert,

Thanks for the reply. I am a little confused with so many month fields in your code with the same field name 'Month'.

If my original month name in the source file is 'MONTH' and when i am loading it to qlikview i want to change it to

[Sales Month] then how the Month field will be aligned in the code?

Thanks,

Sk

[Anonymous]

Hi Skawal,

So the key here I believe is the use of the dual function. Here is a good article on the dual function. In short, it will basically allow you to associate the MONTH field, which is a string, to a number.

Hope it helps to understand

How to use- Dual()

[ramasaisaksoft]

Hi Ska,

why don't u use Month(MONTH) it will give number of the month else num(Month(MONTH)).

if my understanding is wrong please mention which output you  want?

[Gysbert_Wassenaar]

Oops, you're right. I didn't use the correct field names everywhere. I've edited the code. The [Sales Month] field is created as a dual field. That means it has a text value for display purposes and a numeric value that can be used for calculations and sorting. If you sort the [Sales Month] field by number ascending you'll see the month names (the display format) in the correct order too.

[tamilarasu]

Instead of ApplyMap, you can try,

Num(Month(Date#(Left(MONTHFIELD,3),'MMM'))) as [Sales Month]

[#8]

Hi Skawal22,

Try using this.

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 MonthNumber,

LOAD * INLINE [

    Month,MonthNumber

    Jan,1

    Feb,2

    Mar,3

    Apr,4

    May,5

    Jun,6

    Jul,7

    Aug,8

    Sep,9

    Oct,10

    Nov,11

    Dec,12

];

Regards,

Rohan

[MayilVahanan]

Hi

Try like this

Dual(Capitalize(Left(MONTH,3)), Num(Month(Date#(Left(MONTH,3),'MMM')))) as SalesMonth

Num(SalesMonth) gives Number like 1,2,3

SalesMonth gives Values like Jan, Feb