Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
(
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.
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);
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';)
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.