how to convert Month numbers into Month Names

[#1]

Hi ,

My table has a field called Payment Month which is in number. I wanted to have it in Month Names. I wrote a SQL Script in my load using case function and got the Month Names when i tried to get the Names in the List box it is not sorted properly. It is not in the proper Order.

How can i get this.Please help.

Thanks.

[hector]

Hi, you can use 2 options

1. Use an INLINE to make the relationship and sort by "load order"
2. use your field with something like this

month(makedate(2010,yourfield) ) as MonthName

PS. i've use 2010 hardcoded, but you can use some year field

Rgds

[#3]

Hi,

You can even use SET Months in Main tab to change everything to Jan,Feb,Mar,Apr etc

Happy Qliking

Sunil

[#4]

If your data is like this:

LOAD * INLINE [

MonthNum

1

2

3

4

5

6

7

8

9

10

11

12

];

then you can use this expression: =Month(AddMonths(0,MonthNum))

I hope this is the solution..

[#5]

I tried these options nothing worked for me. I have the month as numbers.Can i know how to use set variables ex:i have set monthnames in my Main and how it gets linked to the month that i pull from my database.

can anyone tell me the solution for this.Any other method is also welcome.

[Miguel_Angel_Baeyens]

Hello,

I sometimes set

SET LongMonthNames='Jan','Feb','Mar','Apr','Jun','Jul','Aug','Sep','Oct','Nov','Dec';

Then you can use

=Pick(3, $(LongMonthNames))

which will return "Mar". Use your month field instead of the number.

Hope this helps.

[fernandotoledo]

hI Miguel!

Is it an array? Always wondered how to do it! Thanks!

Here goes an alternative solution in the script presented before in this discussion

hope its useful

Fernando

[Miguel_Angel_Baeyens]

Hello Fernando,

It's not an array, at least not in QlikView, it's just playing with variables to get the value I'm looking for.

Values are quoted since they are literals, and quoting is required when matching literal strings. The Pick() function (I'm using it here to get a string) does the opposite of the Match() function (it would be useful in the opposite case, when I've got a string and want to get the numerical value).

If you are not looking for any additional value to the month name (say, an underlying numeric/date value) than the month name itself, it should be enough to get it done.

Regards.

[johnw]

Suprasanna wrote:I tried these options nothing worked for me. I have the month as numbers.Can i know how to use set variables ex:i have set monthnames in my Main and how it gets linked to the month that i pull from my database.
can anyone tell me the solution for this.Any other method is also welcome.

If your months were simple numbers 1-12, then month(makedate(2010,yourfield)) as Héctor suggested should work. To prove this to yourself, make a text box for =month(makedate(2010,7)). On my machine, I get "Jul". I suspect nobody's solutions are working for you because your month is NOT a simple number 1-12, or because you're not using anyone's solutions correctly in your code.

[fernandotoledo]

Hi again!

Nice explanation!

I knew those functions, but what is new for me is using it with a variable and making it work as an array or vector. I thought pick and match would only aply to fields.

Thanks again!

Fernando