Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

toa
Not applicable

XML part of XLS data - how to extract

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

1 Solution

Accepted Solutions
rnt
Not applicable

Re: XML part of XLS data - how to extract

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

3 Replies
marcus_sommer
Not applicable

Re: XML part of XLS data - how to extract

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

...

next

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

- Marcus

rnt
Not applicable

Re: XML part of XLS data - how to extract

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

toa
Not applicable

Re: XML part of XLS data - how to extract

Thanks Ralf and Marcus,

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