Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
May be try this
Table:
CrossTable(Month, Sum, 3)
LOAD * INLINE [
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
];
FinalTable:
LOAD *,
Date(MakeDate(Year, Month), 'MMM-YYYY') as MonthYear,
'Q' & Ceil(Month/3) as Quarter;
LOAD Year,
Customer,
Product,
Month(Date#(KeepChar(Month, '0123456789'), 'M')) as Month,
Sum
Resident Table;
DROP Table Table;
You could set the "SumX" columns as a YearMonth combination then bring in a master calendar that joins against that column for additional calendar attributes.
May be try this
Table:
CrossTable(Month, Sum, 3)
LOAD * INLINE [
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
];
FinalTable:
LOAD *,
Date(MakeDate(Year, Month), 'MMM-YYYY') as MonthYear,
'Q' & Ceil(Month/3) as Quarter;
LOAD Year,
Customer,
Product,
Month(Date#(KeepChar(Month, '0123456789'), 'M')) as Month,
Sum
Resident Table;
DROP Table Table;