Multiple data tables on one Excel Sheet

[NielBerg]

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

[techvarun]

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

[rwunderlich]

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