topic Re: Convert months (numeric) into text in QlikView

Convert months (numeric) into text

Fri, 27 Feb 2015 14:14:33 GMT

Hi Experts

I have a file that contains months in a numeric format. How can I convert the numeric values into text for the months? (Jan, Feb, e.g.)

Thank you for your help!

Jan

Re: Convert months (numeric) into text

Fri, 27 Feb 2015 14:19:56 GMT

Hi jan,

In your load script you set the date format = MON/DD/YYYY;

For Month kindly use MON i hope it would help you to convert.. try it.

Regards,

Vignesh

Re: Convert months (numeric) into text

robert_mika — Fri, 27 Feb 2015 14:20:56 GMT

month(MakeDate(2000,YourNUmberFiled))

Re: Convert months (numeric) into text

buzzy996 — Fri, 27 Feb 2015 14:21:30 GMT

try to use MONTH(UR FIELD NAME)

Re: Convert months (numeric) into text

Fri, 27 Feb 2015 17:46:55 GMT

Depending on what you're tying to do, you can also use:

pick(your_field_name, 'JAN', 'FEB', 'MAR, ...., 'NOV', 'DEC');

This is assuming your_field_name is a number between 1 and 12.

Regards!

Re: Convert months (numeric) into text

maxgro — Fri, 27 Feb 2015 18:09:32 GMT

you can use

subfield('$(MonthNames)', ';', yourmonthnumber)

also in the script, example

load

rowno() as Month,

subfield('$(MonthNames)', ';', rowno()) as MonthName

AutoGenerate 12;

MonthNames is defined at the beginning of the script

Re: Convert months (numeric) into text

JonnyPoole — Sat, 28 Feb 2015 03:16:20 GMT

Another one:

if your values are 1,2,3 etc....

use an expression like this to interpret the numbers as months and then display them in 'mmm' format ( 'Jan','Feb','Mar' ....)

Date(Date#(MonthNumber,'M'),'MMM') as Month

Sample script:

numbers:

LOAD * INLINE [

MonthNumber

];

Dates:

load * , Date(Date#(MonthNumber,'M'),'MMM') as Month

Resident numbers;

drop table numbers;

Re: Convert months (numeric) into text

preminqlik — Sat, 28 Feb 2015 03:34:48 GMT

Load *,

text(Date(Date#(fieldname,'MM'),'MMM')) as MonthName

from path;

Re: Convert months (numeric) into text

mukesh24 — Sat, 28 Feb 2015 04:31:08 GMT

hi,

Simply load inline table in data model or join this table

DIS_MONTH:

LOAD * INLINE [

MONTH1, DISPLAY_MTN

1, Jan

2, Feb

3, Mar

4, Apr

5, May

6, Jun

7, Jul

8, Aug

9, Sep

10, oct

11, Nov

12, Dec

];

Re: Convert months (numeric) into text

Mon, 02 Mar 2015 08:33:39 GMT

Awesome! Thank you!

Re: Convert months (numeric) into text

hariprasadqv — Mon, 02 Mar 2015 08:41:30 GMT

Hi,

use dual function or MMM format with date.

Re: Convert months (numeric) into text

hic — Mon, 02 Mar 2015 08:48:16 GMT

If you want your month names automatically sorted - i.e. as dual values - you should use the solution suggested by Robert_Mika:

Month(MakeDate(2015,MonthNumber))

Month names created with Text() will be sorted alphabetically, which you don't want.

An inline table will work, but this demands an additional table, when you really want to convert month numbers to month names when loading another table, i.e. inside another Load statement.

HIC