Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have attached example file (Budget_exampledata) where sales person have added budget for year 2015 per month (B2015) which I would like to import. The question is now that that is has excess data and also normal cross table doesn't fit as it has two rows as qualifiers or how to say..
First rows as example:
MonthNum | 1 | 1 | 2 | 2 | 3 | 3 | 4 | 4 | 5 | 5 | 6 | 6 | 7 | 7 | 8 | 8 | 9 | 9 | 10 | 10 | 11 | 11 | 12 | 12 | ||
Product | Salesperson | Zone | TOT2014 | B2015 | TOT2014 | B2015 | TOT2014 | B2015 | TOT2014 | B2015 | TOT2014 | B2015 | TOT2014 | B2015 | TOT2014 | B2015 | TOT2014 | B2015 | TOT2014 | B2015 | TOT2014 | B2015 | TOT2014 | B2015 | TOT2014 | B2015 |
FREEZER | KIA | AP | E | 777 | E | 54 | E | 0 | E | 0 | E | 0 | E | 0 | 0 | E | 0 | 754 | 0 | E | 0 | 0 | 0 | 321 | 0 | 432 |
And the result should be (colors are just to give the hint what data is what):
Product | Salesperson | Zone | Datatype | Date | Amount |
FREEZER | KIA | AP | Budget | 1.1.2015 | 777 |
FREEZER | KIA | AP | Budget | 1.2.2015 | 54 |
FREEZER | KIA | AP | Budget | 1.8.2015 | 754 |
FREEZER | KIA | AP | Budget | 1.11.2015 | 321 |
FREEZER | KIA | AP | Budget | 1.12.2015 | 432 |
So the TOT2014 rows are not needed. As they are in the file just to give the hint for sales person for his totals previous years. Also the year (in yeallow) is not mandatory to get from the file, that can be done manually.
-Juha
Hi Juha,
Interesting load! What I would suggest is a cross table to initially and then a resident load to apply the new field and also handle the final formatting. I have tested the below script on your sample data and it works.
A
InitialLoad:
CrossTable(Month, Data, 3)
LOAD F1 as Product,
F2 as Salesperson,
MonthNum,
[11] as [01/01/2015],
[21] as [01/02/2015],
[31] as [01/03/2015],
[41] as [01/04/2015],
[51] as [01/05/2015],
[61] as [01/06/2015],
[71] as [01/07/2015],
[81] as [01/08/2015],
[91] as [01/09/2015],
[101] as [01/10/2015],
[112] as [01/11/2015],
[121] as [01/12/2015]
FROM
[..\Downloads\Budget_exampledata.xlsx]
(ooxml, embedded labels, table is Budget);
FormattedTable:
LOAD
Product,
Salesperson,
MonthNum AS Zone,
'Budget' AS Datatype,
Month,
Data AS Amount
Resident InitialLoad
Where Product<>'Product'
;
Drop Table InitialLoad;
Hi Juha,
Interesting load! What I would suggest is a cross table to initially and then a resident load to apply the new field and also handle the final formatting. I have tested the below script on your sample data and it works.
A
InitialLoad:
CrossTable(Month, Data, 3)
LOAD F1 as Product,
F2 as Salesperson,
MonthNum,
[11] as [01/01/2015],
[21] as [01/02/2015],
[31] as [01/03/2015],
[41] as [01/04/2015],
[51] as [01/05/2015],
[61] as [01/06/2015],
[71] as [01/07/2015],
[81] as [01/08/2015],
[91] as [01/09/2015],
[101] as [01/10/2015],
[112] as [01/11/2015],
[121] as [01/12/2015]
FROM
[..\Downloads\Budget_exampledata.xlsx]
(ooxml, embedded labels, table is Budget);
FormattedTable:
LOAD
Product,
Salesperson,
MonthNum AS Zone,
'Budget' AS Datatype,
Month,
Data AS Amount
Resident InitialLoad
Where Product<>'Product'
;
Drop Table InitialLoad;
Hi,
one slightly more generic approach without hard coded field names could be:
tabSalesBudget:
CrossTable(ColNam, Amount, 3)
LOAD *
FROM [http://community.qlik.com/servlet/JiveServlet/download/674518-140654/Budget_exampledata.xlsx]
(ooxml, no labels, header is 2 lines, table is Budget, filters(Transpose(),Transpose()))
Where RecNo()<=16;
mapColNam:
mapping LOAD
'@'&(RecNo()+3),
MakeDate(Right(@2,4),@1)
FROM [http://community.qlik.com/servlet/JiveServlet/download/674518-140654/Budget_exampledata.xlsx]
(ooxml, no labels, table is Budget, filters(Transpose(),Remove(Row, Pos(Top, 3)),Remove(Row, Pos(Top, 2)),Remove(Row, Pos(Top, 1))))
Where @2 like 'B*';
Right Join (tabSalesBudget)
LOAD Distinct
ColNam,
Date(ApplyMap('mapColNam', ColNam)) as Date
Resident tabSalesBudget
Where ApplyMap('mapColNam', ColNam);
DROP Field ColNam;
tabTemp:
CrossTable(ColNum, ColNam2)
LOAD 1, *
FROM [http://community.qlik.com/servlet/JiveServlet/download/674518-140654/Budget_exampledata.xlsx]
(ooxml, no labels, header is 1 lines, table is Budget, filters(Transpose(),Transpose()))
Where RecNo()=1;
mapColNam2:
Mapping LOAD ColNum, ColNam2 Resident tabTemp;
DROP Table tabTemp;
RENAME Fields using mapColNam2;
Hope this helps
Regards
Marco