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% |
