Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
maybe
Date(MakeDate(2000, ValueLoop(1,12)), 'MMM')
Another way
Month(Date#(ValueLoop(1, 12), 'M'))
It's not working. I am getting blank as output
It would welcome if there is another way of doing things. I mean except valueloop function, something in script to achieve this
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;
As suggested by Massiomo, You can tweak your expression as
Date(MakeDate(Year, Month), 'MMM') in the script
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:
How to use - Master-Calendar and Date-Values
hope this helps
regards
Marco
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
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,