topic Re: How to Convert Month Number to Month Name in QlikView

How to Convert Month Number to Month Name

Samuelgomes — Tue, 15 Dec 2020 17:48:15 GMT

I have the VER_MES field and the Content of this field is numeric and I need to convert it to the name of the Month.
 How to do this?
Here's how I'm extracting the information

LIB CONNECT TO [SIGAOFC];

[VER010]:
SELECT "VER_FILIAL" AS "%FILIAL",
"VER_PREFIX",
"VER_AGRUPA",
"VER_MES",
"VER_ANO",
"VER_PE",
"VER_VALOR",
"VER_QTDVEI",
"VER_CODFIL",
"VER_CODVEN",
"VER_CODMAR",
"VER_MODVEI",
"D_E_L_E_T_",
"R_E_C_N_O_",
"VER_XPERBR",
"VER_XPERPR",
"VER_XPEROU",
"VER_XPERDI",
"VER_XBRONZ",
"VER_XPRATA",
"VER_XOURO",
"VER_XDIAMA",
"VER_XDIAS"
FROM SIGAOFC.dbo.VER010;

exit script;

Re: How to Convert Month Number to Month Name

Vegar — Tue, 15 Dec 2020 18:18:25 GMT

Try one of the following

Month(date#(VER_MES, 'M')) as Month

Month (makedate (2000,VER_MES)) as Month

Re: How to Convert Month Number to Month Name

Samuelgomes — Tue, 15 Dec 2020 22:42:46 GMT

return this error When using the Month (makedate (2000,VER_MES)) as Month or Month(date#(VER_MES, 'M')) as Month

ERROR [42000] 'makedate' is not a recognized built-in function name

[VER010]:

SELECT

"VER_FILIAL" AS "%FILIAL",

"VER_PREFIX",

"VER_AGRUPA",

Month (makedate (2000,VER_MES)) as Month,

"VER_ANO",

"VER_PE",

"VER_VALOR",

"VER_QTDVEI",

"VER_CODFIL",

"VER_CODVEN",

"VER_CODMAR",

"VER_MODVEI",

"D_E_L_E_T_",

"R_E_C_N_O_",

"VER_XPERBR",

"VER_XPERPR",

"VER_XPEROU",

"VER_XPERDI",

"VER_XBRONZ",

"VER_XPRATA",

"VER_XOURO",

"VER_XDIAMA",

"VER_XDIAS"

FROM SIGAOFC.dbo.VER010

Re: How to Convert Month Number to Month Name

Samuelgomes — Tue, 15 Dec 2020 22:42:00 GMT

return this error When using the Month (makedate (2000,VER_MES)) as Month or Month(date#(VER_MES, 'M')) as Month

ERROR [42000] 'makedate' is not a recognized built-in function name

[VER010]:

SELECT

"VER_FILIAL" AS "%FILIAL",

"VER_PREFIX",

"VER_AGRUPA",

Month (makedate (2000,VER_MES)) as %Month,

"VER_ANO",

"VER_PE",

"VER_VALOR",

"VER_QTDVEI",

"VER_CODFIL",

"VER_CODVEN",

"VER_CODMAR",

"VER_MODVEI",

"D_E_L_E_T_",

"R_E_C_N_O_",

"VER_XPERBR",

"VER_XPERPR",

"VER_XPEROU",

"VER_XPERDI",

"VER_XBRONZ",

"VER_XPRATA",

"VER_XOURO",

"VER_XDIAMA",

"VER_XDIAS"

FROM SIGAOFC.dbo.VER010

Re: How to Convert Month Number to Month Name

Nicole-Smith — Tue, 15 Dec 2020 23:06:44 GMT

You are putting a Qlik function inside of your SQL code (and it's not a SQL function so it doesn't recognize it). You'll need to use it as a preceding load for it to work:

[VER010]: LOAD *, MonthName(MakeDate(VER_ANO,VER_MES)) AS %Month; SELECT "VER_FILIAL" AS "%FILIAL", "VER_PREFIX", "VER_AGRUPA", "VER_MES", "VER_ANO", "VER_PE", "VER_VALOR", "VER_QTDVEI", "VER_CODFIL", "VER_CODVEN", "VER_CODMAR", "VER_MODVEI", "D_E_L_E_T_", "R_E_C_N_O_", "VER_XPERBR", "VER_XPERPR", "VER_XPEROU", "VER_XPERDI", "VER_XBRONZ", "VER_XPRATA", "VER_XOURO", "VER_XDIAMA", "VER_XDIAS" FROM SIGAOFC.dbo.VER010;