topic Re: Excel import, bit more complicated in QlikView

Excel import, bit more complicated

Fri, 05 Dec 2014 20:37:45 GMT

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

Re: Excel import, bit more complicated

Fri, 05 Dec 2014 21:41:17 GMT

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.

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;

Re: Excel import, bit more complicated

MarcoWedel — Sun, 07 Dec 2014 02:34:13 GMT

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