Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Change and clean text

Hye, I have a problem I tried to solve during 3days and I didn't find any solutions...

I want to be able to "clean" some text datas. Indeed, I have a field named "COMMENT", they are many thing I arrived to change.

My first problem was that some caracters came out rong like "é" who went "é". I succeded in changing this caracter right with the replace function : replace(COMMENT, 'é' , 'é')

My question is this I have a text who is like that :

<font size="2" face="sans-serif">Bonjour,</font><br> <br> <font size="2" face="sans-serif">je ne peux pas accéder aux portails client, problèmes connection internet et j'ai besoin de saisir des commandes.</font><br> <br> <br>

I tried to use the replace function with the textbetween() one like this :

replace(COMMENT,TextBetween(COMMENT,'<','>'), ' ')

My goal is to have this at the end :

Bonjour, je ne peux pas accéder aux portails client, problèmes connection internet et j'ai besoin de saisir des commandes.


does somebody knows how to di this? thank-you

5 Replies
ramoncova06
Specialist III
Specialist III

you can use this, though this only applies for this type of text

=TextBetween (Comment,'face="sans-serif">','<',1)&' '&TextBetween (Comment,'face="sans-serif">','<',2)

Not applicable
Author

Hy thank you but the problem is that their is a loooooot of diferents thing to change, I only gave an exemple of it but the problem is that I realy have something like this :

<p><b>TR: Request Number : S_XXXXXXXX - Credentials of Windows, Lotus and SAP   </b></p><br><br><br><p><p><p lang="FR" link="blue" vlink="purple"><div class="WordSection1"><p><span style="color:#1f497d"> </span></p><p><span style="color:#1f497d">Le poste est démarré pour config IP : 10.52.10.65</span></p><div><p><span style="color:#1f497d"> </span></p><p><span style="font-size:10.0pt;font-family:&quot;Calibri Light&quot;,&quot;sans-serif&quot;;color:black">Cordialement<br><br>Fuat OZDEMIR<br>Contrôle de Gestion<br>MECAPLAST Group <br>Site de VIRE<br><a href="mailto:fozdemir@mecaplast.com">fozdemir@mecaplast.com</a><br>Tél : 02 31 66 13 24<br>Fax : 02 31 66 13 29</span><span style="font-family:&quot;Calibri Light&quot;,&quot;sans-serif&quot;;color:#1f497d"></span></p></div><p><span style="color:#1f497d"> </span></p><div><div style="border:none;border-top:solid #b5c4df 1.0pt;padding:3.0pt 0cm 0cm 0cm"><p><b><span style="font-size:10.0pt;font-family:&quot;Tahoma&quot;,&quot;sans-serif&quot;">De :</span></b><span style="font-size:10.0pt;font-family:&quot;Tahoma&quot;,&quot;sans-serif&quot;"> FUAT OZDEMIR [mailto:<a href="mailto:fozdemir@mecaplast.com">fozdemir@mecaplast.com</a>] <br><b>Envoyé :</b> lundi 5 janvier 2015 15:15<br><b>À :</b> Helpdesk FRIZ; &#39;<a href="mailto:hotlinemeca@mecaplast.com">hotlinemeca@mecaplast.com</a>&#39;<br><b>Objet :</b> RE: Request Number : S_XXXXXXXX - Credentials of Windows, Lotus and SAP</span></p></div></div><p> </p><p><span style="color:#1f497d">Bonjour,</span></p><p><span style="color:#1f497d"> </span></p><p><span style="color:#1f497d">J'ai mis le poste à configurer sur le réseau IP : 10.52.10.65.</span></p><p><span style="color:#1f497d">Merci de bien vouloir reconfigurer le portable sur la dernière version Seven.</span></p><p><span style="color:#1f497d"> </span></p><p><span style="color:#1f497d">Nommé le poste 58QUALMACANDELIER</span></p><p><span style="color:#1f497d">Installer outlook</span></p><p><span style="color:#1f497d">Garder lotus</span></p><p><span style="color:#1f497d">Mettre l'accès internet</span></p><p><span style="color:#1f497d">SAP</span></p><p><span style="color:#1f497d"> </span></p><p><span style="color:#1f497d">Identifiant :</span></p><p><span style="color:#1f497d">mcandelier</span></p><p><span style="color:#1f497d">mange82cand</span></p><p><span style="color:#1f497d"> </span></p><p><span style="color:#1f497d"> </span></p><p><span style="color:#1f497d"> </span></p><p><span style="color:#1f497d"> </span></p><div><p><span style="font-family:&quot;Calibri Light&quot;,&quot;sans-serif&quot;;color:#1f497d">Bonjour,</span></p><p><span style="color:#1f497d"> </span></p><p><span style="font-size:10.0pt;font-family:&quot;Calibri Light&quot;,&quot;sans-serif&quot;;color:black">Cordialement<br><br>Fuat OZDEMIR<br>Contrôle de Gestion<br>MECAPLAST Group <br>Site de VIRE<br><a href="mailto:fozdemir@mecaplast.com">fozdemir@mecaplast.com</a><br>Tél : 02 31 66 13 24<br>Fax : 02 31 66 13 29</span><span style="font-family:&quot;Calibri Light&quot;,&quot;sans-serif&quot;;color:#1f497d"></span></p></div><p><span style="color:#1f497d"> </span></p><div><div style="border:none;border-top:solid #b5c4df 1.0pt;padding:3.0pt 0cm 0cm 0cm"><p><b><span style="font-size:10.0pt;font-family:&quot;Tahoma&quot;,&quot;sans-serif&quot;">De :</span></b><span style="font-size:10.0pt;font-family:&quot;Tahoma&quot;,&quot;sans-serif&quot;"> Helpdesk FRIZ [<a href="mailto:helpdesk@mecaplast.com">mailto:helpdesk@mecaplast.com</a>] <br><b>Envoyé :</b> lundi 5 janvier 2015 12:11<br><b>À :</b> FUAT OZDEMIR<br><b>Objet :</b> RE: Request Number : S_XXXXXXXX - Credentials of Windows, Lotus and SAP</span></p></div></div><p> </p><p><span style="color:#1f497d">Bonjour Fuat,</span></p><p><span style="color:#1f497d"> </span></p><p><span style="color:#1f497d">Bonne et heureuse année à toi aussi.</span></p><p><span style="color:#1f497d"> </span></p><p><span style="color:#1f497d">Pour SAP, merci de transmettre le formulaire en pièce jointe directement à _HBC.</span></p><p><span style="color:#1f497d"> </span></p><p><span style="color:#1f497d">Pour les accès portails, à quels portails souhaite-t-elle accéder exactement ?</span></p><p><span style="color:#1f497d"> </span></p><div><p style="text-autospace:none"><span style="font-size:10.0pt;font-family:&quot;Verdana&quot;,&quot;sans-serif&quot;;color:#1f497d">Cordialement / Best Regards </span></p><p style="margin-top:12.0pt;text-autospace:none"><b><i><span style="font-size:10.0pt;font-family:&quot;Arial&quot;,&quot;sans-serif&quot;;color:black">Laurent CLAIN</span></i></b><span style="font-size:10.0pt;font-family:&quot;Arial&quot;,&quot;sans-serif&quot;;color:black"><br>- Support Helpdesk -<b> </b></span><b><i><span style="font-size:10.0pt;font-family:&quot;Arial&quot;,&quot;sans-serif&quot;;color:#c20000"><br>MECAPLAST Group</span></i></b><span style="font-size:10.0pt;font-family:&quot;Arial&quot;,&quot;sans-serif&quot;;color:black"><br>ZI La Mode BP12<br>F-01580 Izernore<br>Tel : 00 33 4 74 49 70 70<br>e-mail : <a href="mailto:helpdesk@mecaplast.com" target="_blank">helpdesk@mecaplast.com</a></span></p><p><span style="font-size:10.0pt;font-family:&quot;Tahoma&quot;,&quot;sans-serif&quot;;color:black">Une demande ? un incident ? envoyez un mail à hotline MECA<br>la boîte mail _HBC/mecaplast traite toutes les demandes concernant SAP</span></p></div><p><span style="color:#1f497d"> </span></p><div><div style="border:none;border-top:solid #b5c4df 1.0pt;padding:3.0pt 0cm 0cm 0cm"><p><b><span style="font-size:10.0pt;font-family:&quot;Tahoma&quot;,&quot;sans-serif&quot;">De :</span></b><span style="font-size:10.0pt;font-family:&quot;Tahoma&quot;,&quot;sans-serif&quot;"> FUAT OZDEMIR [mailto:<a href="mailto:fozdemir@mecaplast.com">fozdemir@mecaplast.com</a>] <br><b>Envoyé :</b> lundi 5 janvier 2015 10:43<br><b>À :</b> Helpdesk FRIZ; _ HBC<br><b>Objet :</b> Request Number : S_XXXXXXXX - Credentials of Windows, Lotus and SAP</span></p></div></div><p> </p><p>Bonjour,</p><p> </p><p>Tous mes v_ux et une bonne année 2015.</p><p> </p><p>Voici la demande d'accès à internet et création login SAP signé par le DU.</p><p> </p><p>Marie-Ange CANDELIER remplace Didier Giffaut (parti)  en tant que Responsable qualité.</p><p> </p><p>Merci de bien vouloir donner les mêmes accès sur internet. ( Qui donne accès au portail client ?)</p><p> </p><p>Concernant SAP merci de vouloir donner les mêmes autorisations qu'avait Didier et l'intégrer dans la liste des signatures de DA. </p><p><span style="color:#1f497d"> </span></p><div><p><span style="font-size:10.0pt;font-family:&quot;Calibri Light&quot;,&quot;sans-serif&quot;;color:black">Cordialement<br><br>Fuat OZDEMIR<br>Contrôle de Gestion<br>MECAPLAST Group <br>Site de VIRE<br><a href="mailto:fozdemir@mecaplast.com">fozdemir@mecaplast.com</a><br>Tél : 02 31 66 13 24<br>Fax : 02 31 66 13 29</span></p></div><p><span style="color:#1f497d"> </span></p><div><div style="border:none;border-top:solid #b5c4df 1.0pt;padding:3.0pt 0cm 0cm 0cm"><p><b><span style="font-size:10.0pt;font-family:&quot;Tahoma&quot;,&quot;sans-serif&quot;">De :</span></b><span style="font-size:10.0pt;font-family:&quot;Tahoma&quot;,&quot;sans-serif&quot;"> Helpdesk FRIZ [mailto:<a href="mailto:helpdesk@mecaplast.com">helpdesk@mecaplast.com</a>] <br><b>Envoyé :</b> vendredi 19 décembre 2014 08:05<br><b>À :</b> FUAT OZDEMIR<br><b>Cc :</b> Helpdesk FRIZ<br><b>Objet :</b> Request Number : S_XXXXXXXX - Credentials of Windows, Lotus and SAP</span></p></div></div><p> </p><p style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:&quot;Helv&quot;,&quot;sans-serif&quot;;color:black">Hello,</span></p><p style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:&quot;Helv&quot;,&quot;sans-serif&quot;;color:black"> </span></p><p style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:&quot;Helv&quot;,&quot;sans-serif&quot;;color:black">For the new user </span><span lang="EN-US">Marie-Ange CANDELIER </span><span lang="EN-US" style="font-size:10.0pt;font-family:&quot;Helv&quot;,&quot;sans-serif&quot;;color:black">All the credentials were created</span></p><p style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:&quot;Helv&quot;,&quot;sans-serif&quot;;color:black"> </span></p><p style="text-autospace:none"><b><span style="font-size:10.0pt;font-family:&quot;Helv&quot;,&quot;sans-serif&quot;;color:black"> </span></b></p><p style="text-autospace:none"><b><span lang="EN-US" style="font-size:10.0pt;font-family:&quot;Helv&quot;,&quot;sans-serif&quot;;color:black">If need, you will find attached the Internet request and SAP account request .</span></b></p><p style="text-autospace:none"><b><span lang="EN-US" style="font-size:10.0pt;font-family:&quot;Helv&quot;,&quot;sans-serif&quot;;color:black"> </span></b></p><p style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:&quot;Helv&quot;,&quot;sans-serif&quot;;color:black">=&gt; <b>Internet request</b> : Any request must be validated by the person in charge, thank you for sending back it to Helpdesk</span></p><p style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:&quot;Helv&quot;,&quot;sans-serif&quot;;color:black"> </span></p><p style="text-autospace:none"><span lang="EN-US" style="font-size:9.0pt;font-family:&quot;Helv&quot;,&quot;sans-serif&quot;;color:black">EN - Internet Access request.pdf  </span></p><p style="text-autospace:none"><span lang="EN-US" style="font-size:9.0pt;font-family:&quot;Helv&quot;,&quot;sans-serif&quot;;color:black"> </span></p><p style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:&quot;Helv&quot;,&quot;sans-serif&quot;;color:black">=&gt; <b>SAP account request</b> : Fill the form and send back it to _HBC.</span></p><p style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:&quot;Helv&quot;,&quot;sans-serif&quot;;color:black"> </span></p><p style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:&quot;Helv&quot;,&quot;sans-serif&quot;;color:black"> </span></p><p style="text-autospace:none"><span lang="EN-US" style="font-size:9.0pt;font-family:&quot;Helv&quot;,&quot;sans-serif&quot;;color:black">Request_account_SAP.xls  </span></p><p style="text-autospace:none"><span lang="EN-US" style="font-size:9.0pt;font-family:&quot;Helv&quot;,&quot;sans-serif&quot;;color:black"> </span></p><p style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:&quot;Helv&quot;,&quot;sans-serif&quot;;color:black">To be able to configure the account of messaging system, can you send us the IP adress of the computer.</span></p><p style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:&quot;Helv&quot;,&quot;sans-serif&quot;;color:black"> </span></p><p style="text-autospace:none"><span lang="EN-US" style="font-size:10.0pt;font-family:&quot;Helv&quot;,&quot;sans-serif&quot;;color:black"> </span></p><p><b><i><span lang="EN-US" style="font-size:10.0pt">Equipe HELPDESK</span></i></b><span lang="EN-US"><br>- Support Helpdesk -</span><span lang="EN-US" style="font-size:10.0pt"> <b><i><span style="color:#c20000"><br>MECAPLAST Group</span></i></b></span><span lang="EN-US"><br>ZI La Mode BP12<br>F-01580 Izernore<br>Tel : 00 33 4 74 49 70 70<br>e-mail : </span><a href="mailto:helpdesk@mecaplast.com"><span lang="EN-US" style="font-size:8.0pt;font-family:&quot;Arial&quot;,&quot;sans-serif&quot;">helpdesk@mecaplast.com</span></a></p></div></p></p></p>

swuehl
MVP
MVP

Maybe have a look into the solutions suggested here:

Removing HTML markup code

Anonymous
Not applicable
Author

The below method will strip all HTML leaving you just the text. 

Data:

LOAD Comment as HTMLRecord

FROM

;

HtmlTag_Map:

MAPPING LOAD DISTINCT '<' & TextBetween('<' & SubField(HTMLRecord, '<'),'<','>') & '>' as HtmlTag, '' as Substitute  //Map in all HTML tags that exist in the HTML of details

Resident Data;

Comments:

LOAD

Trim(MapSubstring('HtmlTag_Map', HTMLRecord)) as Comment

Resident Data;

Not applicable
Author

Great It worked thanks a lot!!