Importing Excel data with subtotal header rows

[schow]

I'm trying to import into QlikSense from a monthly excel file that looks like the table below. It has different subheadings for Equities and Foreign Currency and below those subheadings there can be rows that appear multiple times (Australia, Canada, UK in this example).

Excel Data:

	Weight
Equities	123.0%
Australia	78.9%
Canada	42.8%
United Kingdom	1.4%
Foreign Currency	136.4%
Australia	13.3%
Canada	64.8%
United Kingdom	58.2%

I'm trying to import this into Qliksense and have the below result, where Australia would have Equity and Foreign Currency in 2 seperate columns, can anyone help? The Excel file typically will have data in consistent rows (i.e. Australia Foreign Currency in the same row each time, UK Equities same row each time) but the ideal solution would not rely on consistent row numbers or spacing to get this result. 

The Subtotal categories are predefined (i.e. they will always be the same Equities or Foreign Currency) but it's possible that the countries under each heading could change.

Desired Result:

	Equities	Foreign Currency
Australia	78.9%	13.3%
Canada	42.8%	64.8%
United Kingdom	1.4%	58.2%

[edwin]

there are two steps t this.  first load your data in a table format not report format:

LOAD
    if(match(Weight,'Equities', 'Foreign Currency')>0 ,Weight, Peek(Category)) as Category,
    if(match(Weight,'Equities', 'Foreign Currency')=0 ,Weight) as Country,
    F2 as Amount
FROM [....xlsx]
(ooxml, embedded labels, table is Sheet1);

[edwin]

you can actually stop there and use expressions to create your table.

you can explore pivoting your data to get it in a table form that you wanted:

here is a way to do it:

https://community.qlik.com/t5/Qlik-Design-Blog/The-Generic-Load/ba-p/1473470

or if you have very limited measures:

XLS: LOAD
if(match(Weight,'Equities', 'Foreign Currency')>0 ,Weight, Peek(Category)) as Category,
if(match(Weight,'Equities', 'Foreign Currency')=0 ,Weight) as Country,
F2 as Amount FROM [....xlsx](ooxml, embedded labels, table is Sheet1);
NoConcatenate XLSdata:load Country, Amount as Equities
Resident XLS where Category='Equities' and not isnull(Country);
inner join (XLSdata) load Country, Amount as [Foreign Currency]
Resident XLS where Category='Foreign Currency' and not isnull(Country);
drop table XLS;

[edwin]

this assumes that a country will always have both categories, else do a full outer join

[schow]

@edwin  Thanks for your answer. Unfortunately the countries are not consistent between categories. I.e. you might have some countries in Equities that are not in Foreign Currency and vice versa. I should have included that in my post.

[schow]

For now I'm loading each section of the excel file seperately based on the row numbers and categorizing it manually something like this below (there are a few more fields in my data set but i just kept it simple in my example).

LOAD
date AS EFF_DATE,
F1 AS Category,
'Equities' as "Asset_Class",
"Exposure" as "Weight",
"Exposure as Var" as "Var weight"
FROM [file.xls]
(ooxml, embedded labels, header is 2 lines, table is Sheet1)
where RecNo()>=5 AND RecNo()<=9;

And then repeating that for each asset class category (equities, fx etc) with different RecNo() numbers to get all the different asset classes. This works but I feel like there must be a better way? I'm worried that the rows and categories may at some point change which would require a lot of recoding. The excel input file is from an external source so i can't control it's formatting. 

[edwin]

The categories should be consistent even if the countries arent

this shows you the full outer join

https://help.qlik.com/en-US/sense/May2021/Subsystems/Hub/Content/Sense_Hub/Scripting/combine-tables-...

[schow]

I tried this on my simple example table and this is the result I'm getting. It has the right columns but all of the data is lost in the transformation. Think I might be missing something here.

Could you explain more on how an outer join would solve this problem? The core issue i'm struggling to understand is how the script will find the header rows (eg "Equity") and then code that header into a new column only for the rows immediately below the header row (up until it finds a blank row, which will be followed by another header row). 

There's about 8 different Headers that are predefined, so I can have those in a set list before hand. 

[edwin]

dont use line numbers that will be a problem down the road.

if you follow the code and just remove the "inner" and leave it as just JOIN, even if the countries are consistent, you will stil get the right result based on your samples.  this one i added other countries to make them not consistent:

[edwin · Accepted solution]

this is what i used:

input:
load * inline [
Category, Weight
Equities,123.0%
Australia,78.9%
Canada,42.8%
United Kingdom,1.4%
Ice Land,42.8%

 	 
Foreign Currency,136.4%
Australia,13.3%
Canada,64.8%
United Kingdom,58.2%

Mexico,64.8%
Argentina,58.2%
];

data:
LOAD
    if(match(Category,'Equities', 'Foreign Currency')>0 ,Category, Peek(Category)) as Category,
    if(match(Category,'Equities', 'Foreign Currency')=0 ,Category) as Country,
    Weight as Amount
resident input;

drop table input;

NoConcatenate XLSdata:load Country, Amount as Equities
Resident data where Category='Equities' and not isnull(Country);

 join (XLSdata) load Country, Amount as [Foreign Currency]
Resident data where Category='Foreign Currency' and not isnull(Country);
drop table data;