Hi!
I have some sales data with Sales per month (only month name columns) which i would like to load into my app.
The data looks like this
| Year | Customer | Product | Sum1 | Sum2 | Sum3 | Sum4.... |
|---|
| 2017 | MrHey | Fanta | 299 | 110 | 0 | 50 |
| 2016 | MrHey | Fanta | 3000 | 500 | 600 | 0 |
| 2016 | MrsBuyer | Plats | 555 | 333 | 0 | 8000 |
| 2015 | Hula Hula INC | Sunscreen | 5456 | 54 | 4 | 5555 |
Where the "SumX" columns represent sales in Jan, Feb, Mar etc. I could ofcourse rename the columns, but the year has to be in the year column.
And the LOAD script looks like this:
Sales:
LOAD
Year,
Customer,
Product,
num(Sum1, '# ##0','.',' ') as Jan,
num(Sum2, '# ##0','.',' ') as Feb,
num(Sum3, '# ##0','.',' ') as Mar,
num(Sum4, '# ##0','.',' ') as Apr,
num(Sum5, '# ##0','.',' ') as May,
num(Sum6, '# ##0','.',' ') as June,
num(Sum7, '# ##0','.',' ') as July,
num(Sum8, '# ##0','.',' ') as Aug,
num(Sum9, '# ##0','.',' ') as Sep,
num(Sum10, '# ##0','.',' ') as Oct,
num(Sum11, '# ##0','.',' ') as Nov,
num(Sum12, '# ##0','.',' ') as Dec
FROM [lib://QlikSenseDB/datatestkolumner.xlsx]
(ooxml, embedded labels, table is Blad1);
Sales_T1:
CROSSTABLE ( Month, Sum, 3 )
LOAD
*
RESIDENT
Sales;
Sales_pivot:
LOAD
Year,
Customer,
Product,
Month,
Sum
RESIDENT
Sales_T1
WHERE
isnum( Sum );
What I would like is to add Quarter without adding more columns in the data source file. How do I do this?
And also, is it possible to improve the " num(Sum1, '# ##0','.',' ') as Jan," part of the LOAD statement (it works but doesn't look optimal)?
Thanks a lot!
