topic Re: String function in QlikView

String function

Anonymous — Mon, 13 Apr 2015 07:39:38 GMT

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> des xxxxxx</p> </div>

<div class="ExternalClassB40DFBB6F0F4475EB22328DBFCD544A1"><p> fgasdfgdfg</p><br></div>

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

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

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

Re: String function

Michiel_QV_Fan — Mon, 13 Apr 2015 07:42:49 GMT

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.

Re: String function

Anonymous — Mon, 13 Apr 2015 08:11:18 GMT

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

Re: String function

Michiel_QV_Fan — Mon, 13 Apr 2015 09:50:38 GMT

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> des xxxxxx</p> </div>

result:  des xxxxxx

and finalist as this:

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

Re: String function

Anonymous — Mon, 13 Apr 2015 12:46:06 GMT

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

Re: String function

Michiel_QV_Fan — Wed, 15 Apr 2015 09:57:29 GMT

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

Re: String function

Ralf-Narfeldt — Wed, 15 Apr 2015 12:59:17 GMT

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  

Load *, Replace(before_p_close, ' ', '') 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> des xxxxxx</p> </div>

<div class="ExternalClassB40DFBB6F0F4475EB22328DBFCD544A1"><p> fgasdfgdfg</p><br></div>

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

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

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

];