String function

[Anonymous]

Hi Guys,

I have a field that contains a lot of systemdata which need to be eliminate. It's source is an XML file. The red and bold is all the information I want.

Please advice.

------

<div class="ExternalClassB40DFBB6F0F4475EB22328DBFCD544A1"><p>&#160;des xxxxxx</p>&#160;</div>

<div class="ExternalClassB40DFBB6F0F4475EB22328DBFCD544A1"><p>&#160;fgasdfgdfg</p><br></div>

<div class="ExternalClassB40DFBB6F0F4475EB22328DBFCD544A1"><p>&#160;Intranet HR bouwen ter vervanging van het oude intranet</p>&#160;</div>

<div class="ExternalClassB40DFBB6F0F4475EB22328DBFCD544A1"><p>Zoekmachine tbv het administreren en snel zoeken van marketing fot's</p>&#160;</div>

<div class="ExternalClassBA22F59909AC4C7F9C3D46B83423B929"><p>Integratie tussen WEB en SFDC v.w.b. (nieuwe) klantgegevens en &#160;serviceverzoeken van (nieuwe) klanten, bv tegelmonsters. Oplossen met Pardot en SFDC.​</p></div>

[Michiel_QV_Fan]

Did you load the xml in QlikView allready. Is this the output from that load or is this the content of the xml?

XML can be loaded and QlikView wil created tables and fields with the data.

After that load you can build filters.

[Anonymous]

Hi Michel,

That’s correct. XML is loaded alrready in the App

Robert Waterval

ICT

Koninklijke Mosa bv

Meerssenerweg 358

P.O. Box 1026

NL-6201 BA Maastricht

T +31 (0)43 368 8803

F +31 (0)43 363 11 77

robert.waterval@mosa.nl<mailto:robert.waterval@mosa.nl>

www.mosa.nl<http://www.mosa.nl/>

<http://www.facebook.com/mosatiles> <http://www.linkedin.com/company/royal-mosa>

Van: Michiel van de Goor

Verzonden: maandag 13 april 2015 9:44

Aan: Robert Waterval

Onderwerp: Re: - String function

[Michiel_QV_Fan]

Try the following to eliminate parts of the data:

I have assumed that each line in your original post is 1 line of data with the same column name.

subfield(<your field>, '<p>', 2) as new_field     --> this will remove the first part of the data

subfield(subfield(<your field>, '<p>', 2), '</p>', -1) as new field --> this will remove the first and second part of the data

<div class="ExternalClassB40DFBB6F0F4475EB22328DBFCD544A1"><p>&#160;des xxxxxx</p>&#160;</div>

result: &#160;des xxxxxx

and finalist as this:

subfield(subfield(subfield(<your field>, '<p>', 2), '</p', -1), ';', 1)  as new field

[Anonymous]

Hi Michel,

This wouldn’work. I have past your functions. The result is the DetailsTest fields.

I just need the words; ‘des xxxxxx’

Robert Waterval

ICT

Koninklijke Mosa bv

Meerssenerweg 358

P.O. Box 1026

NL-6201 BA Maastricht

T +31 (0)43 368 8803

F +31 (0)43 363 11 77

robert.waterval@mosa.nl<mailto:robert.waterval@mosa.nl>

www.mosa.nl<http://www.mosa.nl/>

<http://www.facebook.com/mosatiles> <http://www.linkedin.com/company/royal-mosa>

Van: Michiel van de Goor

Verzonden: maandag 13 april 2015 11:51

Aan: Robert Waterval

Onderwerp: Re: - String function

[Michiel_QV_Fan]

Can you post the unprocessed, original, content of the field in a txt or xlsx file. I look into it.

[Ralf-Narfeldt]

Based on the small sample data you showed this should do the job. But if there are more complex structures in the XML  there is most likely more complex processing needed.

// Strip out &#160;

Load *, Replace(before_p_close, '&#160;', '') As cleaned;

//Cut at </p> tag

Load *, SubField(after_p,'</p>',1) As before_p_close;

//Cut at p tag

Load *, SubField(divline,'<p>',2) As after_p;

//Load data

Load * inline [

divline

<div class="ExternalClassB40DFBB6F0F4475EB22328DBFCD544A1"><p>&#160;des xxxxxx</p>&#160;</div>

<div class="ExternalClassB40DFBB6F0F4475EB22328DBFCD544A1"><p>&#160;fgasdfgdfg</p><br></div>

<div class="ExternalClassB40DFBB6F0F4475EB22328DBFCD544A1"><p>&#160;Intranet HR bouwen ter vervanging van het oude intranet</p>&#160;</div>

<div class="ExternalClassB40DFBB6F0F4475EB22328DBFCD544A1"><p>Zoekmachine tbv het administreren en snel zoeken van marketing fot's</p>&#160;</div>

<div class="ExternalClassBA22F59909AC4C7F9C3D46B83423B929"><p>Integratie tussen WEB en SFDC v.w.b. (nieuwe) klantgegevens en &#160;serviceverzoeken van (nieuwe) klanten, bv tegelmonsters. Oplossen met Pardot en SFDC.</p></div>

];