topic Re: Remove unwanted text from a String in QlikView

Remove unwanted text from a String

brf10043 — Wed, 25 Nov 2020 16:16:04 GMT

I'm pulling data from a sharepoint list and some of the text has extra unwanted characters.

How would I remove "<span aria-hidden="true"></span>" from the string below

<span aria-hidden="true"></span>Lets all work 3 days a week

remove everything except "Lets all work 3 days a week" from the string below?

<div class="ExternalClass450FFA875469469B9098549F4A621B80"><p><span aria-hidden="true"></span>Lets all work 3 days a week</p></div>

Re: Remove unwanted text from a String

devarasu07 — Thu, 20 Apr 2017 18:39:52 GMT

temp:

LOAD * INLINE [

Field

];

Fact:

Load stripHTML(Field) as FieldCleaned

Resident temp;

Re: Remove unwanted text from a String

devarasu07 — Thu, 20 Apr 2017 18:42:24 GMT

Hi,

Refer to the attachment,

Re: Remove unwanted text from a String

vishsaggi — Thu, 20 Apr 2017 18:45:19 GMT

Try this

= Subfield(Subfield('<div class="ExternalClass450FFA875469469B9098549F4A621B80"><p><span aria-hidden="true"></span>Lets all work 3 days a week</p></div>', '</span>',2), '<')

Re: Remove unwanted text from a String

vishsaggi — Thu, 20 Apr 2017 18:49:04 GMT

stripHTML() is this new function in V12.0 ?

Re: Remove unwanted text from a String

brf10043 — Thu, 20 Apr 2017 18:53:36 GMT

Could be. I can't get it to work in 11.

Re: Remove unwanted text from a String

devarasu07 — Thu, 20 Apr 2017 18:54:40 GMT

Hi,

nope, it's vbscript function

4GuysFromRolla.com

Re: Remove unwanted text from a String

brf10043 — Thu, 20 Apr 2017 18:55:47 GMT

I'm not sure I follow this. It appears that you are hardcoding this. I can't predict what the text I want to keep will be.

"Lets all work 3 days a week" is just on of the entries.

Re: Remove unwanted text from a String

brf10043 — Thu, 20 Apr 2017 18:57:37 GMT

The actual column is "ows_TO_x002d_BE"

Re: Remove unwanted text from a String

devarasu07 — Thu, 20 Apr 2017 19:02:21 GMT

Hi,

It's just vbscript only. it working fine in QV 11 also

try below steps,

1) in your qvw document. Press Ctrl+M (open macros windows)

2) try to do below settings and

Re: Remove unwanted text from a String

brf10043 — Thu, 20 Apr 2017 19:06:29 GMT

Unfortunately, I'm not sure this will work. We have hundreds of users and I will not be able install a macro for each one.

Unless I can set it up on the original file and it would work for the users using AJAX in IE.

Re: Remove unwanted text from a String

devarasu07 — Thu, 20 Apr 2017 19:09:19 GMT

Hi,

Another solution using qlikview script method (no macro in this method)

temp:

LOAD * INLINE [

Field

];

Data:

LOAD RecNo() as RecNo, Field Resident temp;

drop table temp;

HtmlTag_Map:

MAPPING LOAD DISTINCT '<' & TextBetween('<' & SubField(Field, '<'),'<','>') & '>' as HtmlTag, '' as Substitute

Resident Data;

JOIN(Data) LOAD RecNo, MapSubstring('HtmlTag_Map', Field) as Field_Cleansed

Resident Data;

For more details, refer

Removing HTML markup code

rbecher reply

Re: Remove unwanted text from a String

vinieme12 — Thu, 20 Apr 2017 19:18:57 GMT

The below works

EDITTEXT:

Mapping Load * inline [

From,to

>,@{

<,}@

];

LOAD

textbetween(Mapsubstring('EDITTEXT',replace(FIELDIS,'><','_')),'@{','}@') as FieldIS

;

LOAD if(Match(right(Trim(FIELDIS),1),'>')=0,FIELDIS&'<',FIELDIS) as FIELDIS inline [

FIELDIS

<span aria-hidden="true"></span>Lets all work 3 days a week line 2 test

];

Re: Remove unwanted text from a String

vishsaggi — Thu, 20 Apr 2017 19:40:03 GMT

I dont know your column name so hardcoded it. You can use your column name like

= Subfield(Subfield(ows_TO_x002d_BE, '</span>',2), '<')

When you say it is not always the same text, i know it wont be but will that text be between

</Span> ...... </P> always or what are the combination of tags you have to look to pull your text. Can you share some sample data or example html tags. ?

Re: Remove unwanted text from a String

brf10043 — Thu, 20 Apr 2017 20:28:02 GMT

Sorry, I probably should have given you guys more information.

When I load ows_TO_x002d_BE, and load it into a table object I get the following.

I then tried this load TextBetween(ows_TO_x002d_BE, '<p>', '</p>') which resulted in

It works for two of my cells but not the middle one. It sill has the extract character string. I am not sure why the middle row has additional extra characters.

I really appreciate your assistance.

Re: Remove unwanted text from a String

vinieme12 — Thu, 20 Apr 2017 20:43:11 GMT

If you try the script exactly the way I have posted, it'll work

Re: Remove unwanted text from a String

sumitjadhav — Fri, 21 Apr 2017 09:43:24 GMT

Hi Bobby,

Try this Expression,

-->> SubField(SubField(SubField(Field,'</span>',2),'><',1),'<',1)

It Will Produce an Output:

Thank's & Regards,

Sumit Jadhav.