topic Re: XML part of XLS data - how to extract in QlikView

XML part of XLS data - how to extract

Teppo_Ojala — Mon, 11 May 2015 11:23:53 GMT

Hi,

I found couple of quite similar cases here, but yet I haven't solved my problem.

Attached a xls file that consists of two columns

A = Group name

B = XML information about which products belongs to the GROUP from column A. One GROUP can have 1-X products.

In this example GROUP 1 should have 4 products (A, B, C and D) - and GROUP 2 should include only two products (A and B).

I couldn't write a script that would return a data model with 1 or 2 tables where groups are linked to correct products.

Hopefully somebody here has skills for this

Re: XML part of XLS data - how to extract

marcus_sommer — Mon, 11 May 2015 14:28:37 GMT

Just as idea. At first I would read the file normal as xls. Then I would loop through all records and pick with peek() the content and create from xml-content a load-statement:

xls:

Load * From xls;

for i = 0 to fieldvaluecount('Product') - 1

let vGroup = peek('Group', $(i), 'xls');

let vProduct = peek('Product', $(i), 'xls');

...

Here is a similar case: Re: Load Data from gisgraphy / xml source

- Marcus

Re: XML part of XLS data - how to extract

Ralf-Narfeldt — Mon, 11 May 2015 14:30:02 GMT

Or a crude parsing:

Directory;

Source:

LOAD Group,

Product

FROM

temp.xlsx

(ooxml, embedded labels, table is [Koontikyselyn erikoishaun n...]);

Temp:

Load Group,

SubField(Product, 'value uiname=') As ProductRaw

resident Source;

Result:

Load Group,

TextBetween(ProductRaw, '"', '"') As Product1

resident Temp where Left(ProductRaw, 1)<> '<';

Re: XML part of XLS data - how to extract

Teppo_Ojala — Wed, 13 May 2015 09:56:21 GMT

Thanks Ralf and Marcus,

I just used Ralf's script and it worked perfectly!