topic Re: Convert Month Number to Month Name in Straight Table in QlikView

Convert Month Number to Month Name in Straight Table

Wed, 25 Nov 2020 16:16:04 GMT

I have a text file as a source which gives me product sales for every month. Now some product were not sold on a particular month, so it is not specified in that source file.

Now i want to display it in a straight table having those month(not existing in source file) with sales value 0. I used ValueLoop() in dimension as well as expression to calculate.

User want Month to be displayed in 'Jan', 'Feb' format and i am not able to convert valueloop() output in 'MMM' format. Please Help.

Example

Source format

Product Category	Product Name	Month	Year	Sales
Clothing	Jeans	1	2016	100
Furniture	Chair	2	2016	500
Furniture	Table	2	2016	150
Clothing	Tshirt	5	2016	452
Clothing	Shirt	4	2016	550
Furniture	Table	4	2016	465

Output in similar format

Product Category	Product Name	Month	Year	Sales
Clothing	Jeans	JAN	2016	100
Clothing	Tshirt	MAY	2016	452
Clothing	Shirt	APR	2016	550
Clothing	Jeans	FEB	2016	0
Clothing	Jeans	MAR	2016	0
Clothing	Jeans	APR	2016	0
Clothing	Jeans	MAY	2016	0
Clothing	Jeans	JUN	2016	0
Clothing	Jeans	JUL	2016	0
Clothing	Jeans	AUG	2016	0
Clothing	Jeans	SEP	2016	0
Clothing	Jeans	OCT	2016	0
Clothing	Jeans	NOV	2016	0
Clothing	Jeans	DEC	2016	0
Clothing	Tshirt	FEB	2016	0
Clothing	Tshirt	MAR	2016	0
Clothing	Tshirt	APR	2016	0
Clothing	Tshirt	MAY	2016	0
Clothing	Tshirt	JUN	2016	0
Clothing	Tshirt	JUL	2016	0
Clothing	Tshirt	AUG	2016	0
Clothing	Tshirt	SEP	2016	0
Clothing	Tshirt	OCT	2016	0
Clothing	Tshirt	NOV	2016	0
Clothing	Tshirt	DEC	2016	0
Clothing	Shirt	FEB	2016	0
Clothing	Shirt	MAR	2016	0
Clothing	Shirt	APR	2016	0
Clothing	Shirt	MAY	2016	0
Clothing	Shirt	JUN	2016	0
Clothing	Shirt	JUL	2016	0
Clothing	Shirt	AUG	2016	0
Clothing	Shirt	SEP	2016	0
Clothing	Shirt	OCT	2016	0
Clothing	Shirt	NOV	2016	0
Clothing	Shirt	DEC	2016	0
Furniture	Chair	FEB	2016	500
Furniture	Table	FEB	2016	150
Furniture	Table	APR	2016	465

Re: Convert Month Number to Month Name in Straight Table

maxgro — Thu, 15 Dec 2016 19:58:49 GMT

maybe

Date(MakeDate(2000, ValueLoop(1,12)), 'MMM')

Re: Convert Month Number to Month Name in Straight Table

sunny_talwar — Thu, 15 Dec 2016 20:24:01 GMT

Another way

Month(Date#(ValueLoop(1, 12), 'M'))

Re: Convert Month Number to Month Name in Straight Table

Thu, 15 Dec 2016 20:59:46 GMT

It's not working. I am getting blank as output

Re: Convert Month Number to Month Name in Straight Table

Thu, 15 Dec 2016 21:01:27 GMT

It would welcome if there is another way of doing things. I mean except valueloop function, something in script to achieve this

Re: Convert Month Number to Month Name in Straight Table

rupamjyotidas — Thu, 15 Dec 2016 21:50:18 GMT

Maybe in Script: You can use this script to change the month to MMM

Main:

LOAD [Product Category],

[Product Name],

Month,

Year,

Sales,

[Product Category]&[Product Name]&Month as Key

FROM

[https://community.qlik.com/thread/243552]

(html, codepage is 1252, embedded labels, table is @1);

Temp:

Load Distinct

[Product Category],

[Product Name],

Year

Resident Main;

Join(Temp)

LOAD * INLINE [

Month

];

NoConcatenate

Tenp1:

Load

0 as Sales

Resident Temp;

Drop Table Temp;

Concatenate(Main)

Load

* ,

1 as Flag

Resident Tenp1 where NOT Exists(Key,[Product Category]&[Product Name]&Month);

Drop table Tenp1;

Re: Convert Month Number to Month Name in Straight Table

neelamsaroha157 — Thu, 15 Dec 2016 22:09:55 GMT

As suggested by Massiomo, You can tweak your expression as

Date(MakeDate(Year, Month), 'MMM') in the script

Re: Convert Month Number to Month Name in Straight Table

MarcoWedel — Thu, 15 Dec 2016 22:26:59 GMT

Hi,

using Sunny's solution in the script would look like:

tabSales:
LOAD [Product Category],
    [Product Name],
    Month(Date#(Month,'M')) as Month,
    Year,
    Sales
FROM [https://community.qlik.com/thread/243552] (html, codepage is 1252, embedded labels, table is @1);

please notice that although looking similar the different suggested ways to create your Month field are not equivalent:

The underlying numerical values of the dual Month fields are the month numbers 1 to 12 when creating like Month(...) while the value represents a date when creating like Date(... , 'MMM').

Maybe also helpful:

QlikView Date fields

Get the Dates Right

How to use - Master-Calendar and Date-Values

hope this helps

regards

Marco

Re: Convert Month Number to Month Name in Straight Table

nagarjuna_kotha — Fri, 16 Dec 2016 04:33:19 GMT

try This :

If u want use in Front End Use this :

=Pick(Match(Month,1,2,3,4,5,6,7,8,9,10,11,12),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

If u want use in BackEnd Use this :

Pick(Match(Month,1,2,3,4,5,6,7,8,9,10,11,12),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec') as MonthName

Hope this Helps

-Nagarjun

Re: Convert Month Number to Month Name in Straight Table

Anonymous — Fri, 16 Dec 2016 11:05:21 GMT

Hi,

You can use applymap also in script like following

MonthMap:

Load * Inline [

Month, MonthName

1,Jan

2,Feb

3,Mar

4,Apr

5,May

6,Jun

7,Jul

8,Aug

9,Sep

10,Oct

11,Nov

12,Dec

];

tabSales:

LOAD [Product Category],

[Product Name],

Applymap('MonthMap',Month) as MonthName,

Year,

Sales

From......

Thanks,