Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
asb02512
Contributor III
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
shraddha_g
Partner - Master III
Partner - Master III

Try to calculate month as below:

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

View solution in original post

5 Replies
shraddha_g
Partner - Master III
Partner - Master III

Try to calculate month as below:

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

asb02512
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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 ......

Lech_Miszkiewicz
Partner Ambassador/MVP
Partner Ambassador/MVP

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.
shraddha_g
Partner - Master III
Partner - Master III

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