topic Re: what is wrong in my script? in QlikView

what is wrong in my script?

ali_hijazi — Wed, 02 Jan 2019 12:51:04 GMT

Hello
I got a table called CASES_PEGA in which there is a field called PYDESCRIPTION which contains HTML tags
I'm trying to strip HTML codes and keep the text of PYDESCRIPTION field
I'm using the following script:

HtmlTag_Map:Mapping
LOAD DISTINCT
'<' & TextBetween('<' & SubField(PYDESCRIPTION, '<', IterNo()),'<','>') & '>' as HtmlTag,
'' as Substitute
Resident CASES_PEGA
While NOT IsNull(SubField(PYDESCRIPTION, '<', IterNo()));

Left Join(CASES_PEGA)
temp:
Load Distinct
CASE_ID
,MapSubstring('HtmlTag_Map', PYDESCRIPTION) as Field_Cleansed
Resident CASES_PEGA;

However I'm getting a GENERAL SCRIPT ERROR when executing the MapSubstring

kindly advise

Re: what is wrong in my script?

sunny_talwar — Wed, 02 Jan 2019 12:21:30 GMT

Would you be able to share few rows of data to test this out? or share your logfile?

Re: what is wrong in my script?

ali_hijazi — Wed, 02 Jan 2019 12:52:05 GMT

Hello @sunny_talwar
check attached Excel file

this is the source of my data

Re: what is wrong in my script?

sunny_talwar — Wed, 02 Jan 2019 13:18:55 GMT

I am not sure what might be going on here... may be @rwunderlich can comment on this as he mentioned a similar problem here those uncaught store errors

For reference, I tried using this script

CASES_PEGA:
LOAD PYDESCRIPTION
FROM [..\..\Downloads\tages_2.xlsx]
(ooxml, embedded labels, table is Sheet1);

HtmlTag_Map:
Mapping
LOAD DISTINCT
	 '<' & TextBetween('<' & SubField(PYDESCRIPTION, '<', IterNo()),'<','>') & '>' as HtmlTag,
	 '' as Substitute
Resident CASES_PEGA
While NOT IsNull(SubField(PYDESCRIPTION, '<', IterNo()));

TRACE I AM HERE 1;

SET ErrorMode = 0;

FinalCASES_PEGA:
LOAD RowNo() as RowNum,
	 PYDESCRIPTION,
	 MapSubString('HtmlTag_Map', PYDESCRIPTION) as Field_Cleansed
Resident CASES_PEGA;

TRACE I AM HERE 2;

DROP Table CASES_PEGA;

SET ErrorMode = 1;

and even though I have set the errormode to 0, the script errors out.

Re: what is wrong in my script?

rwunderlich — Wed, 02 Jan 2019 19:03:34 GMT

MapSubstring() tends to throw errors when you use a mapping table that has keys longer than about 18k characters. I'm not sure of the exact number, it seems to vary by a few hundred depending on circumstances. In the example used in this thread there a number of keys > 26K chars.

A simpler way, using a module function, to extract the contents of HTML elements can be found here.

Qlikview Cookbook: Strip HTML From Text http://qlikviewcookbook.com/recipes/download-info/strip-html-from-text/

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: what is wrong in my script?

sunny_talwar — Wed, 02 Jan 2019 19:05:38 GMT

Thanks Rob!! You are the best.
Is this issue discussed in more details elsewhere? I would like to read more about it if possible.

Thanks,
Sunny

Re: what is wrong in my script?

rwunderlich — Wed, 02 Jan 2019 19:34:43 GMT

Hi Sunny,

Thanks for the compliment. I have not seen it discussed anywhere except in my own head. Here's a test script that demonstrates the issue. Note that 18000 sometimes works for me, sometimes not. 20000 never works.

Map:
Mapping
LOAD
Repeat('A',RecNo())
,' '
AutoGenerate 20000
;

Data:
LOAD
'A' as Key
,MapSubString('Map', 'A') as Mapped
AutoGenerate 1
;

-Rob

Re: what is wrong in my script?

sunny_talwar — Wed, 02 Jan 2019 19:46:34 GMT

This is strange... it did work for me with 20000, but started to error out after 20,500.... Is this a problem with just MapSubString() or have you noticed a similar issue with ApplyMap()?

Re: what is wrong in my script?

rwunderlich — Wed, 02 Jan 2019 20:09:41 GMT

I've only observed it with MapSubstring().

Re: what is wrong in my script?

sunny_talwar — Wed, 02 Jan 2019 20:24:04 GMT

Thank you Rob!!

Re: what is wrong in my script?

ali_hijazi — Thu, 03 Jan 2019 08:06:06 GMT

thank you @rwunderlich, and @sunny_talwar for your replies

So the problem is in the MapSubstring() function

the thing is that I posted the question in QlikView related space but I'm actually running this script in Qlik Sense so I cannot use the suggested alternative of writing a macro

Kindly advise

Re: what is wrong in my script?

rwunderlich — Thu, 03 Jan 2019 19:18:27 GMT

You may be able to use the RexEx Connector https://help.qlik.com/en-US/connectors/Subsystems/Web_Connectors_help/Content/Connectors_QWC/Data-Source-Connectors/RegEx-Connector.htm using the pattern I gave in the macro.

-Rob

Re: what is wrong in my script?

polisetti — Tue, 29 Jun 2021 13:39:12 GMT

Hi @sunny_talwar / @rwunderlich ,

Just wanted to check the alternative approach in the above case. I am facing the same issue and looking for another approach to remove all the HTML tags <> from a field. Could you please help me out????

Thanks,

Jaswanth P

Re: what is wrong in my script?

Felipe_Violin — Mon, 06 Feb 2023 19:29:01 GMT

Rob, in your example you use the StripHtml function.

qlik sense does not exist this function,
do you know if there is an alternative?

Re: what is wrong in my script?

rwunderlich — Mon, 06 Feb 2023 23:41:06 GMT

You could use the RegEx connector with the replace function: https://help.qlik.com/en-US/connectors/Subsystems/Web_Connectors_help/Content/Connectors_QWC/Data-Source-Connectors/RegEx-Connector.htm

-Rob