topic Re: Month Number to Month Name Tricky one in QlikView

Month Number to Month Name Tricky one

BootCam — Wed, 25 Nov 2020 16:16:04 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 Tricky one

Gysbert_Wassenaar — Thu, 03 Mar 2016 17:22:17 GMT

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

...

;

Re: Month Number to Month Name Tricky one

BootCam — Thu, 03 Mar 2016 19:32:30 GMT

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,

Re: Month Number to Month Name Tricky one

Anonymous — Thu, 03 Mar 2016 23:17:39 GMT

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()

Re: Month Number to Month Name Tricky one

ramasaisaksoft — Fri, 04 Mar 2016 07:11:14 GMT

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?

Re: Month Number to Month Name Tricky one

Gysbert_Wassenaar — Fri, 04 Mar 2016 07:11:48 GMT

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.

Re: Month Number to Month Name Tricky one

tamilarasu — Fri, 04 Mar 2016 07:26:18 GMT

Instead of ApplyMap, you can try,

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

Re: Month Number to Month Name Tricky one

Fri, 04 Mar 2016 07:30:01 GMT

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

Re: Month Number to Month Name Tricky one

MayilVahanan — Fri, 04 Mar 2016 08:22:35 GMT

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