topic Re: Loading Table without Certain Attributes in QlikView

Loading Table without Certain Attributes

Anonymous — Tue, 10 May 2016 17:39:20 GMT

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.

Re: Loading Table without Certain Attributes

trdandamudi — Tue, 10 May 2016 17:48:18 GMT

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);

Re: Loading Table without Certain Attributes

Anonymous — Tue, 10 May 2016 18:24:39 GMT

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';)

Re: Loading Table without Certain Attributes

MindaugasBacius — Tue, 10 May 2016 18:44:03 GMT

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.