Convert Month Number to Month Name in Qlik

Convert Month Number to Month Name in Qlik

Several methods exist for date conversion and formatting. It depends on where the date comes from and how one wishes to use it to determine which function or feature suits the requirement best.

Whether receiving a month as a number or evaluating a date to find the Month, we often need to change the Month’s format.

To evaluate and return a month as the Month’s name is the best to set the value into a date before passing the date into the functions.

To evaluate a month from a standard date field, simply wrap the date with

Month(), Date() or MonthName()

 functions:

Month(OrderDate) as Month;

This will return your month in the Format set in the Initial Load Script

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

Alternatively, one can use Date() with formatting to specify the return value:

Date(myField, 'MMMM');         - Full Month Name

Date(myField, 'MMM');         - Three letter abbreviation

Date(myField, 'MM');                - Numeric value in 2 chars

Date(myField, 'M');                - Numeric value as integer..

Pay attention to where you are trying to accomplish this. It needs to be within a resident or preceding LOAD statement to use Qlik syntax as described. This syntax will not work in a SQL statement and is intended for use in the Qlik portion of the update.

Why not simply use the MonthName function?

Qlik has a MonthName function. Could we use MonthName() to evaluate and return the value? Similarly to Month (), MonthName requires a date as the input.

MonthName(date[, period_no])

The return for MonthName, however, includes the year. It is therefore simpler to use the Month() or Date() Functions. The period_no is an integer that will return the following or previous month name under the value of the integer; positive values will step ahead in month names, by the value of the integer, while negative values will return previous months names, stepping back by the value of the negative integer.

Up Next: Learn Qlik Scalability Tools