topic Re: Converting numbers to month name in QlikView

Converting numbers to month name

Tue, 04 Nov 2014 07:52:20 GMT

Hi,

Can some one help me in converting the numbers to month names?

I want to remove this number and just want to have the month names.

or is there any possibility to just change these numbers to month names.

Initially i used if statement to bring the month names. But i dont know how to replace the numbers with month names. Thank you.

Re: Converting numbers to month name

tresB — Tue, 04 Nov 2014 07:54:16 GMT

Try like:

Month(MakeDate(1,MonthNum)) as Month

Re: Converting numbers to month name

its_anandrjs — Tue, 04 Nov 2014 07:56:53 GMT

Provide sample file or may be you can try MonthName( DateField ) if you have any date field in the data model.

Regards

Anand

Re: Converting numbers to month name

Tue, 04 Nov 2014 07:59:57 GMT

Hi,

Sorry its not working out. Any other suggestions?

Thank you.

Re: Converting numbers to month name

its_anandrjs — Tue, 04 Nov 2014 08:01:30 GMT

You can try this also in load script

Num( Month( Date( DateField,'YYYYMM' ) )) as NumMonth

Num( Month(DateField) ) as NumMonth

Ex:-

=Num( Month( Date( Today(),'YYYYMM' ) ))

Regards

Anand

Re: Converting numbers to month name

Tue, 04 Nov 2014 08:03:27 GMT

Hi,

This is the actual field

Here i need to change the numbers into months .

Thank you.

Re: Converting numbers to month name

tresB — Tue, 04 Nov 2014 08:06:26 GMT

Try like:

Month(MakeDate(1,Mid([SPEND MONTH],5))) as Month

Re: Converting numbers to month name

its_anandrjs — Tue, 04 Nov 2014 08:07:07 GMT

Try

Num( Month( Date( [SPEND MONTH],'YYYYMM' ) ) ) as NumMonth

Num( Month( [SPEND MONTH] ) ) as NumMonth

Regards

Anand

Re: Converting numbers to month name

Peter_Cammaert — Tue, 04 Nov 2014 08:07:59 GMT

Try

Month(MakeDate(1,num(right([SPEND MONTH], 2)))) as Month

Re: Converting numbers to month name

its_anandrjs — Tue, 04 Nov 2014 08:10:40 GMT

I added and update the tresesco expression as

Month(MakeDate(1,Mid([SPEND MONTH],5,2))) as Month

Month(MakeDate(1,right([SPEND DATE], 2))) as Month

Regards

Anand

Re: Converting numbers to month name

Peter_Cammaert — Tue, 04 Nov 2014 08:10:42 GMT

This will also work (and much simpler)

Month('1/' & right([SPEND MONTH], 2) & '/2014') AS Month

because it doesn't matter in what year you place your month, as long as it's a month.

Re: Converting numbers to month name

Tue, 04 Nov 2014 08:12:30 GMT

Hi anand, should i do it expression tab or should i load it in the script?

Thank you

Re: Converting numbers to month name

its_anandrjs — Tue, 04 Nov 2014 08:16:48 GMT

In load script is better but you can go for expression also but performance wise add another field in the load script.

Regards

Anand

Re: Converting numbers to month name

jagan — Tue, 04 Nov 2014 08:18:36 GMT

Hi,

Try like this in script

LOAD

Date(Date#( '201411','YYYYMM' ), 'MMM' ) AS Month

Date(Date#( '201411','YYYYMM' ), 'YYYY MMM' ) AS YearMonth

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

Re: Re: Converting numbers to month name

Peter_Cammaert — Tue, 04 Nov 2014 08:19:00 GMT

If you want to format this period into a monthname in the UI (straight table or so), see the example in attachment.

BTW uses the same expression as my previous post.

Peter

Re: Converting numbers to month name

MarcoWedel — Tue, 04 Nov 2014 08:24:26 GMT

Month(Date#([SPEND MONTH],'YYYYMM'))

Re: Converting numbers to month name

MK_QSL — Tue, 04 Nov 2014 08:34:23 GMT

Date(Date#(Period,'YYYYMM'),'MMM') as MonthName

Re: Converting numbers to month name

tresB — Tue, 04 Nov 2014 08:49:39 GMT

A word of caution: using Date(...,'MMM') to create month field might not be a good idea. While using date(), you get month names right for the field, but the internal number representation still remains a date. In that case, if you try to get back the month number using Num(), you won't. Here comes the advantage of Month() which returns a dual output (number in the backend and text in the front).

Date(Today(), 'MMM') =>Nov

Num(Date(Today(), 'MMM')) =>41947

Month(Today()) =>Nov

Num(Month(Today()) =>11

I always prefer using Month().

Re: Converting numbers to month name

Tue, 04 Nov 2014 09:22:03 GMT

Sorry guys nothing is working out here, i tried all the examples.

thanks for the reply.

Re: Converting numbers to month name

Anonymous — Tue, 04 Nov 2014 09:29:25 GMT

Try something like below,

Month(Date#(Left(Trim([SPEND MONTH]),6),'YYYYMM'))