Loading Table without Certain Attributes

[Anonymous]

Hello,

I have a table in Excel, which is similar to the file attached, which I am trying to load into QlikView using the following code:

Table:
CrossTable(Product, Cashflow, 5)

LOAD
*
FROM

(ooxml, embedded labels, table is BELCF);

I want to load this table using the Cross Table functionality. However, I do not want to load the columns/rows for 'Total' (greyed out in the attached spreadsheet).

How could I alter my above-noted code to load the table without the 'Total' column/row? I know that it should not be too difficult, but I cannot seem to figure it out.

I appreciate any help.

[trdandamudi]

May be like below:

Table:

CrossTable(Product, Cashflow, 5)

LOAD Year,

     Unit,

     Currency,

     Entity,

     Region,

     [Product A],

     [Product B],

     [Product C]

FROM

(ooxml, embedded labels, table is Sheet71);

[Anonymous]

Unfortunately this will not work because I am looping the load script through a number of workbooks and worksheets, each with different Product Types. However, I did figure out a workaround. I referenced the table as a resident table and then used SQL to remove all Products called 'Total' (LOAD * WHERE [Product] <> 'Total';)

[MindaugasBacius]

CrossTable(Products, CashFlows, 5)

LOAD Year, 
     Unit, 
     Currency, 
     Entity, 
     Region, 
     [Product A], 
     [Product B], 
     [Product C]
FROM

(ooxml, embedded labels, table is Sheet71, filters(
Remove(Col, Pos(Top, 9)),
Remove(Row, Pos(Top, 12)),
));

Take a look to the attachment below.