topic Re: How can I convert numbers to months (i.e. 1 = January) in QlikView

How can I convert numbers to months (i.e. 1 = January)

Thu, 22 Mar 2012 16:08:32 GMT

Hello,

New to Qlikview... please can someone tell me how I can convert numbers to months to that 1 is displayed as Jan, 2 as Feb etc. I have a listbox with the numbers 1 - 12 in - so how can I convert the numbers to text and still retain the functionality?

Thanks,

markp3rry.

How can I convert numbers to months (i.e. 1 = January)

erichshiino — Thu, 22 Mar 2012 16:19:52 GMT

Hi,

There are some alternatives here:

http://community.qlik.com/message/1895#1895

Regards,

Erich

How can I convert numbers to months (i.e. 1 = January)

jykang0638 — Thu, 22 Mar 2012 16:28:26 GMT

You can write a expression to convert number to months in the listbox properties window.
Open listbox properties window and then choose <expression> at the Field in the General tab.
Write expression like below at the Edit Expression window.
=Pick(monthnum, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')
Here, monthnum is the your fileld name.

I hope it will help you.
Steve Kang.

How can I convert numbers to months (i.e. 1 = January)

jagan — Fri, 23 Mar 2012 06:27:02 GMT

Hi,

Create a Mapping table as below

MonthMapping:

LOAD * INLINE [

MonthNo, Month

1, Jan

2, Feb

3, Mar

4, Apr

];

Now use Month field wherever you need.

Note: Your Month number Field name and above month number field should be same. In the above script I used MonthNo. So you replace it with your month field name, so that the two tables will join.

Regards,

Jagan.

How can I convert numbers to months (i.e. 1 = January)

Fri, 23 Mar 2012 09:39:37 GMT

Thanks - I tried Steve and Jagan's methods. Both worked, but I was unable to sort Steve's solution in date order (i.e. Jan to Dec). Jagan's answer sorted perfectly on Load Order.

Re: How can I convert numbers to months (i.e. 1 = January)

Fri, 23 Mar 2012 09:59:54 GMT

you can Create a bucket in the Script(Back/Front).for converting no into string.functionality will remaining as it is

=if(Month=1,'jan',

if(Month=2,'Feb',

if(Month=3,'March',

if(Month=4,'April',

if(Month=5,'May',

if(Month=6,'June',

if(Month=7,'July',

if(Month=8,'August',

if(Month=9,'Septem',

if(Month=10,'OCT',

if(Month=11,'Nov',

if(Month=12,'DEC'))))))))))))

How can I convert numbers to months (i.e. 1 = January)

hic — Fri, 23 Mar 2012 12:35:04 GMT

All the above will work. But I would generate it on the fly using

Month(MakeDate(2012,MonthNo))

/HIC

Re: How can I convert numbers to months (i.e. 1 = January)

IAMDV — Wed, 23 May 2012 10:35:17 GMT

Thanks Henric. This is very useful.

Cheers,

Re: How can I convert numbers to months (i.e. 1 = January)

martynlloyd — Fri, 21 Feb 2014 11:06:11 GMT

Thanks Henric, very elegant, just what I need.

Marty.

Re: How can I convert numbers to months (i.e. 1 = January)

rcorcoran — Fri, 14 Mar 2014 12:29:07 GMT

Hi Jagan

I have tried your solution and it works but it is bringing back all the Months whether data is loaded for a month or not. I only have 2014 and only have Jan data but all months are showing is there a way around this? If I have the numbers it will only show 1 even though there is nothing selected

Thanks

Rhona

Re: How can I convert numbers to months (i.e. 1 = January)

jagan — Fri, 14 Mar 2014 15:52:45 GMT

Hi,

Try like this in script

LOAD

Pick(monthnum, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec') AS Month

FROM Data;

Regards,

Jagan.

Re: How can I convert numbers to months (i.e. 1 = January)

Mon, 04 Jan 2016 18:39:58 GMT

Date# is how Qlikview knows what "1" or "2" means... Then use the "Month" function around it. No hard coding years needed.

Month(Date#([FIELD],'M')) as MONTH

Re: How can I convert numbers to months (i.e. 1 = January)

ramasaisaksoft — Tue, 05 Jan 2016 07:00:59 GMT

Hi Mark,

if script like below

load * inline [

Mon

];

then simply write the expression like below

=if(Mon=1,'Jan',

if(Mon=2,'Feb')) up to 12 months

Re: How can I convert numbers to months (i.e. 1 = January)

ramya_grandhi — Wed, 19 Sep 2018 20:11:00 GMT

Hi pratyush,

I have used the above script but I am not getting the Month names in order like jan, feb, mar...
i am getting in alphabetical order like apr, aug, dec, feb....

Can you please suggest me how can I get that in this order. jan, feb, mar...

I should not add inline table

Thanks in Advance

Re: How can I convert numbers to months (i.e. 1 = January)

hic — Thu, 20 Sep 2018 07:59:49 GMT

You need to create dual values. The simplest way is to use

Month(MakeDate(2018,Month))

but you can also modify Pratyush' script to

=if(Month=1,Dual('jan',1),

if(Month=2,Dual('Feb',2),

if(Month=3,Dual('March',3)

...

HIC