topic Re: Convert month into Month name in QlikView

Convert month into Month name

Anonymous — Wed, 05 Nov 2014 14:04:43 GMT

Hi all!

i have months as numbers in excel file, how can i convert them to Month Names in qlikview

Re: Convert month into Month name

MK_QSL — Wed, 05 Nov 2014 14:07:44 GMT

Use below in your script

Subfield('$(MonthNames)',';',NumberFormatMonthField) as MonthName

Re: Convert month into Month name

its_anandrjs — Wed, 05 Nov 2014 14:16:46 GMT

Hi,

Map the another String Month

Map:

Mapping Load * inline

[

NumMonth,MonthName

1,Jan

2,Feb

3,Mar

4,Apr

5,May

6,June

7,July

8,Aug

9,Sep

10,Oct

11,Nov

12,Dec

];

Fact:

Load

ApplyMap ('Map', NumMonth,) as MonthName

From Source;

Regards

Anand

Re: Convert month into Month name

Peter_Cammaert — Wed, 05 Nov 2014 14:37:28 GMT

Or how about this (much simpler). Assume that the column with month number is called MNum:

LOAD *,

text(Month('1/' & MNum & '/2014')) AS MonthName

FROM YourExcelFile.xlsx (...);

Month() always returns a dual value, and depending on the context it will use the numerical or string representation. Text() forces the use of the string representation.

Re: Convert month into Month name

Peter_Cammaert — Wed, 05 Nov 2014 14:40:30 GMT

Note that, just like Manish's example this solution will use the month names (or abbreviations) that are set at the beginning of your script.

If you prefer full month names or different languages, then use the Mapping Table solution as presented above by Anand.

Good luck,

Peter

Re: Convert month into Month name

its_anandrjs — Wed, 05 Nov 2014 14:49:35 GMT

Hi,

And more specific if you requires perfect monthname field then write like

Map:

Mapping

LOAD NumMonth,Month(Date#(MonthName,'MMM')) as MonthName;

Load * Inline

[

NumMonth,MonthName

1,Jan

2,Feb

3,Mar

4,Apr

5,May

6,June

7,July

8,Aug

9,Sep

10,Oct

11,Nov

12,Dec

];

Fact:

Load

ApplyMap ('Map', NumMonth,) as MonthName

From Source;

Regards

Anand

Re: Convert month into Month name

preminqlik — Wed, 05 Nov 2014 15:00:24 GMT

Load *,

Month(Date#(Monthnumberfield,'MM')) as Monthname

from xxx;

Re: Convert month into Month name

Clever_Anjos — Wed, 05 Nov 2014 15:21:27 GMT

Use the special variable MonthNames to do this

SubField(MonthNames,';',5) will return May

SubField(LongMonthNames,';',6) will return June

Re: Convert month into Month name

Anonymous — Thu, 06 Nov 2014 11:07:23 GMT

Thanks everyone I have solved the problem by using Month(Date#(Monthnumberfield,'MM')) function