topic Re: Removing HTML markup code in QlikView

Removing HTML markup code

mhassinger — Mon, 28 Jan 2013 16:57:13 GMT

I've got a webquery that generates an XML document in the browser. I'm using this as a web file data source in QlikView, and it works as expected, pulling in the XML schema and data. However, one of the fields is full of HTML markup, and I'm not sure the best way to get it all out. Since the XML is generated dynamically on an internet site, it never hits the server file system and so I can't do anything on that end. Also, the HTML is pretty extensive, with lots of things like:

So it's not as simple as a few replace statements to strip and .

Any ideas?

Re: Removing HTML markup code

christian77 — Mon, 28 Jan 2013 17:03:21 GMT

Yeap, it is not that simple.

There also a function subfield(FIELD,'>',2)

Then apply subfield(subfield(FIELD,'>',2),'<',1)

With this last thing, you have something.

For this case, the result would be So it's not as simple as a few replace statements to strip

Somehow you're gonna have to do many tries and maybe several steps in your load.

You could start by spliting your code in lines...

Re: Removing HTML markup code

rbecher — Mon, 28 Jan 2013 17:53:15 GMT

On way could be to pull out all texts between < > into a mapping table and then use mapsubstring to remove all occurances..

I could work out an example if you would upload a doc.

- Ralf

Re: Removing HTML markup code

Gysbert_Wassenaar — Mon, 28 Jan 2013 18:13:53 GMT

Try using a small vbscript function found here. See attached qvw

Re: Removing HTML markup code

rbecher — Mon, 11 Feb 2013 10:53:55 GMT

Based on Gysbert's example, I did it with pure script code using a Mapping Table and MapSubstring() to show the power of QlikView script language and to omit the Macro which should speedup the load:

Data:
LOAD RecNo() as RecNo, Field
FROM comm71215.xls (biff, embedded labels, table is [Sheet1$]);
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;

- Ralf

Re: Removing HTML markup code

Wed, 21 Aug 2013 22:20:50 GMT

Thanks Ralf! works great. I wrote it using an inline - in case anyone is interested:

Test:
LOAD RecNo() as RecNo, html_field inline [
html_field
'<body><p>Hello!</p><p></p><p>DATA TEST DATA TEST </p><p></p><p>TESTING THIS OTHER SEGMENT.</p><p> also this one in lower case</p><p></p><p>lots of different types of html tags <span test="test" __test_macro="testmac" class="test_macro" src="test.gif"></span></p></body>'
];

HtmlTag_Map:
MAPPING LOAD DISTINCT '<' & TextBetween('<' & SubField(html_field, '<'),'<','>') & '>' as HtmlTag, '' as Substitute
Resident Test;

JOIN(Test) LOAD RecNo, MapSubstring('HtmlTag_Map', html_field) as Field_Cleansed
Resident Test;

Re: Removing HTML markup code

Thu, 17 Apr 2014 04:01:54 GMT

Ralf this is fantastic, thank you.

If my xml datafile has multiple html fields (and I need to do this to all of them), how would you recommend I deal with that in my script in the most efficient way?

Many thanks

Re: Removing HTML markup code

rbecher — Thu, 17 Apr 2014 07:10:21 GMT

Hi Karen,

could you upload a sample file?

- Ralf

Re: Removing HTML markup code

Thu, 17 Apr 2014 18:47:28 GMT

Hi Ralf,

For some background for you, I am currently connecting my QV to a SharePoint site where I have an input form. The form includes checkboxes, single line text fields etc including multiline rich text fields (which generates HTML in the XML feed).

My script looks something like:

Decisions:

LOAD

ows_ID as ID,

…..

ows_Decision_x0020_Comments as [Decision Comments],

ows_Appeal as Appeal,

ows_Appeal_x0020_Comments as [Appeal Comments],

ows_ReReview as Re-Review,

ows_ReReview_x0020_Comments as [Re-Review Comments],

…..

From <link to sharepoint>

When i display these fields in QV, the comments fields above output all the HTML tags which is not what I want. Your code is brilliant to remove the html tags from 1 field (e.g. [Decision Comments] but when I have multiple html fields (e.g. Decision Comments, Appeal Comments, Re-Review Comments etc) I would love advice on how best (most efficiently) to deal with that?

at the moment, I have your code following my initial Load:

HTMLTag_Map:

MAPPING LOAD Distinct '<' & TextBetween('<' & SubField([Decision Comments], '<', IterNo()), '<','>') & '>' as HTMLTag, '' as Substitute

Resident Decisions;

Join (Decisions) LOAD ID, MapSubString('HTMLTag_Map', [Decision Comments]) as [Decision Comments Cleansed]

Resident Decisions;

while not ISNULL (SubField([Decision Comments], '<', IterNo()));

But how do i then do the same for the next Comments field e.g. [Appeal Comments] and then [Re-Review Comments}?

Do I do a separate Mapping and join after each field or can I do everything at once? Apologies if I'm missing the obvious, quite new to QV.

Huge Thanks,

Re: Removing HTML markup code

rbecher — Thu, 17 Apr 2014 19:13:34 GMT

Karen,

I would suggest to do the procedure for every field the same way, so 3 times in a row:

Load the Field

Load the Map

Join & Strip the HTML Tags

- Ralf

Re: Removing HTML markup code

Thu, 17 Apr 2014 19:18:39 GMT

Great, thanks so much Ralf. I was planning on doing that but wanted to check whether I was missing a trick or building something very inefficient!

Re: Removing HTML markup code

rbecher — Thu, 17 Apr 2014 19:21:16 GMT

It could work in another way, too:

Load the Fields

Load the Map for Field1 into Map1

Load the Map for Field2 into Map2

Load the Map for Field3 into Map3

Join/Strip with 3x different MapSubstr()

- Ralf

Re: Removing HTML markup code

Wed, 16 Jul 2014 16:26:42 GMT

I'm getting an error when i try this: MapSubString function takes a constant map as first parameter

I'm loading a table from a stored procedure like so:

CRMActivity:

REPLACE SQL EXEC sp_getdata '$(vEmail)';

HtmlTag_Map:

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

Resident CRMActivity

While NOT IsNull(SubField(description, '<', IterNo()));

JOIN(CRMActivity) LOAD activityid, description, objectname, ownername, subject, MapSubstring('HtmlTag_Map', description) as Field_Cleansed

Resident CRMActivity;

The fields returned from the stored procedure are activityid, description, objectname, ownername, subject. I'm trying to clean the description field of html.

Can anyone see what I'm doing wrong?

Re: Removing HTML markup code

rbecher — Wed, 16 Jul 2014 17:01:06 GMT

Strange error, never got that. Can you load the mapping table into a normal table to see what's in it:

HtmlTag_Map:

LOAD DISTINCT '<' & TextBetween('<' & SubField(description, '<', IterNo()),'<','>') & '>' as HtmlTag, '' as Substitute

Resident CRMActivity

While NOT IsNull(SubField(description, '<', IterNo()));

Re: Removing HTML markup code

Wed, 16 Jul 2014 18:59:37 GMT

I tried this and it appears the mapping table is blank. Any suggestions?

Re: Removing HTML markup code

rbecher — Wed, 16 Jul 2014 19:01:48 GMT

Can you give an example what's in table CRMActivity?

Re: Removing HTML markup code

Wed, 16 Jul 2014 19:10:58 GMT

Here's one of the records:

[1000145,

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"><HTML>

</HEAD>

<BODY>

Hello Jackson,

Any idea what the following error means:

Error: Disconnected: No supported methods available

</BODY>

</HTML>

, Email, User1, RE: Support Case-30084]

Re: Removing HTML markup code

rbecher — Wed, 16 Jul 2014 19:37:09 GMT

Maybe we have to purge the line breaks before:

HtmlTag_Map:

MAPPING LOAD DISTINCT '<' & TextBetween('<' & SubField(PurgeChar(description, chr(13)&chr(10)), '<', IterNo()),'<','>') & '>' as HtmlTag, '' as Substitute

Resident CRMActivity

While NOT IsNull(SubField(description, '<', IterNo()));

JOIN(CRMActivity) LOAD activityid, description, objectname, ownername, subject, MapSubstring('HtmlTag_Map', PurgeChar(description, chr(13)&chr(10))) as Field_Cleansed

Resident CRMActivity;

Re: Removing HTML markup code

Wed, 16 Jul 2014 19:52:32 GMT

Same error:

Error in expression:MapSubString function takes a constant map as first parameter

JOIN(CRMActivity) LOAD activityid, description, objectname, ownername, subject, MapSubstring('HtmlTag_Map', PurgeChar(description, chr(13)&chr(10))) as Field_Cleansed

Resident CRMActivity

Thanks for trying. I'm pulling my hair out

Re: Removing HTML markup code

Wed, 16 Jul 2014 19:55:56 GMT

Just to clear this up. The mapping works. Even through it throws that error in the script, the field_cleansed column contains the text without the html. Its just that it throws that error every time a partial refresh is done.