Convert Month Number to Month Name in Straight Table

[#1]

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

[maxgro]

maybe

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

[sunny_talwar]

Another way

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

[#4]

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

[#5]

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

[rupamjyotidas]

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

     1

     2

     3

     4

     5

     6

     7

     8

     9

     10

     11

     12

];

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;

[neelamsaroha157]

As suggested by Massiomo, You can tweak your expression as

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

[MarcoWedel]

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

[nagarjuna_kotha]

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

[Anonymous]

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,