Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
asb02512
New Contributor III

Number to names

Hello All

Need a query to change MonthNumbers to Month Names. The table columns are:

mnthly_sales:

LOAD BRANCH_CD,

  YYMM,

    left(YYMM,4) as year,

    Right(YYMM,2) as Month,

     [CUST#],

    BRANCH_CD&'-'&[SALESMAN#] AS CUSTSALES#,

    [VNDR#]&'-'&[ITEM#] AS ITMVND#,

    [VNDR#],

  CUST_TYPE_CD,

  NET_SALES_VALUE;

MNTHLY_SALES_ACTVTY:

SELECT "BRANCH_CD",

  "YYMM",

    "CUST#",

  "SALESMAN#",

  "VNDR#",

  "ITEM#",

  "CUST_TYPE_CD",

  "NET_SALES_VALUE"

FROM "MBTW"."MNTHLY_SALES_ACTVTY" WHERE YYMM BETWEEN 201201 AND 201612 ;

Output: for the year and month is as shown

1.png 2.png

The requirement is to change the Months format from Numbers to Names (Jan, Feb , Mar......)

for this i tried IF statement

LOAD BRANCH_CD,

  YYMM,

    left(YYMM,4) as year,

    Right(YYMM,2) as Month,

  if(month(YYMM)='1','Jan',

  if(month(YYMM)='2','Feb',

  if(month(YYMM)='3','Mar',

  if(month(YYMM)='4','Apr',

  if(month(YYMM)='5','May',

  if(month(YYMM)='6','Jun',

  if(month(YYMM)='7','Jul',

  if(month(YYMM)='8','Aug',

  if(month(YYMM)='9','Sep',

  if(month(YYMM)='10','Oct',

  if(month(YYMM)='11','Nov',

  if(month(YYMM)='12','Dec')))))))))))) as MonthName,

and got this Output but the issue is some months are not displaying.

3.png

how to fix this ????????????????????????????

1 Solution

Accepted Solutions
Partner
Partner

Re: Number to names

Try to calculate month as below:

month(date(date#(YYMM,'YYYYMM')))

5 Replies
Partner
Partner

Re: Number to names

Try to calculate month as below:

month(date(date#(YYMM,'YYYYMM')))

asb02512
New Contributor III

Re: Number to names

its done thanks Shraddha Can u please tell me Where i can find all the queries or expressions of QLIK . is there any manual for the queries and expressions. and one more question is that when Database updated will the data will get reflects on the Published APPS by default???. Or we need to Configure any rule for this in the QMC.

asb02512
New Contributor III

Re: Number to names

I created a Reload task and i got it   is there any other way to reload without creating a reload task. so that autoupdate will be done on the Published APPS

sorry before trying i raised the question ......

Re: Number to names

It is as simple as going to Qlik help:

2016-12-20_18-01-43.png

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
Partner
Partner

Re: Number to names

You can also provide Qlik Branch this reload button on front end which will allow to reload app whenever required.