topic Re: Multiple data tables on one Excel Sheet in QlikView

Multiple data tables on one Excel Sheet

NielBerg — Tue, 12 Mar 2019 16:18:16 GMT

Hi Guys,

Really need your help with the following:

I have an Excel sheet that contains multiple tables per division. The tables for each division vary in size in terms of the number of rows. The columns are however consistent throughout.

In column A, a division's name appears followed by the column headings starting in column B in the next row. I need to write a script to consolidate the data from all tables (divisions) without having to manually manipulate the Excel file.

I am totally stumped and would appreciate any help I can find. I've attached an example of the file with dummy data.

Hope you can help me.

Cheers,

Niel

Re: Multiple data tables on one Excel Sheet

techvarun — Wed, 13 Mar 2019 06:24:58 GMT

You mean

Label:
First 1 LOAD A
FROM
Example.xlsx
(ooxml, no labels, table is Data);

LET vDepartment = Replace(FieldValue('A',1),'Department:','');

Data:
LOAD '$(vDepartment)' as Department,
     Code, 
     Description, 
     [QTY Sold], 
     [Sold Cost], 
     [Sales Incl VAT], 
     [Sales Excl VAT], 
     Profit, 
     GP%, 
     [Theoretical GP%], 
     [%T/O]
FROM
Example.xlsx
(ooxml, embedded labels, table is Data, filters(
Remove(Row, Pos(Top, 1)),
Remove(Col, Pos(Top, 1))
));

this

Re: Multiple data tables on one Excel Sheet

rwunderlich — Wed, 13 Mar 2019 06:50:43 GMT

Something like this:

Sales:
LOAD
RecNo() as RecId,
if(len(trim(A))=0, peek('Department'), SubField(A,':',2)
) as Department,
B as Code,
C as Description,
D as [QTY Sold],
E as [Sold Cost]
FROM
Example.xlsx
(ooxml, no labels, table is Data);

Inner Join(Sales)
LOAD *
Resident Sales
Where isNum(Code)
;

Hopefully you get the idea and can fill out the remaining columns F, G, etc.

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com