topic Re: MapSubstring, HTML, Clob, script load error no error message. in QlikView

MapSubstring, HTML, Clob, script load error no error message.

weephil76 — Fri, 02 Feb 2018 15:21:43 GMT

Hi,

I'm fairly new to Qlikview so apologies if any of this is just me being silly.

I am trying to use the MapSubstring function to remove HTML formatting from a Oracle CLOB field retrieved from a QVD.

This is the mapping table:

PRLD_HtmlTag_Map:

mapping LOAD

DISTINCT

'<' & TextBetween('<' & SubField([PRLD Asset Long Desc], '<'),'<','>') & '>' as HtmlTag, '' as Substitute

FROM

[..\7.Maintenance\Data\PR_LONGDESC.qvd]

(qvd);

Then I am trying to apply that to the following load from the same QVD:

[PRLDESC]:

LOAD

[PRLD Key] as [%PRLDKeyID],

MapSubstring('PRLD_HtmlTag_Map',[PRLD Asset Long Desc]) as PRLD_Resolved,

[PRLD ContentUID]

FROM

[..\7.Maintenance\Data\PR_LONGDESC.qvd]

(qvd);

I have checked and if I change the mapping load to just a load then I can see HtmlTag and Substitute are populated as expected. When I try and excute the LOAD for PRLDESC then I get no error displayed it just says that the script is finished and then says there has been an error do I wish to reload the last data.

Can anyone tell me what I am doing wrong? Have been stuck on this for days now.

Thanks

Phil

Re: MapSubstring, HTML, Clob, script load error no error message.

Peter_Cammaert — Fri, 02 Feb 2018 16:52:54 GMT

Care to post some example data? A single column in a QVD?

Re: MapSubstring, HTML, Clob, script load error no error message.

weephil76 — Mon, 05 Feb 2018 08:50:53 GMT

No can do I'm afraid, sensitive company data so won't be able to post.

Do you feel this is due to the data, if you can tell me what to look for then I can try and fix this myself?

Re: MapSubstring, HTML, Clob, script load error no error message.

Anil_Babu_Samineni — Mon, 05 Feb 2018 13:40:51 GMT

Interesting !! Can you post few rows how DB carrying?

Re: MapSubstring, HTML, Clob, script load error no error message.

weephil76 — Mon, 05 Feb 2018 14:27:38 GMT

Hi Anil,

Not completely sure what you are asking but if its to post data then no I cannot do that.

Happy to answer any questions anyone has but I'd be astonished if anyone working with company confidential data actually posted their data so I'm pretty astonished that people ask for that.

Thanks

Re: MapSubstring, HTML, Clob, script load error no error message.

Anil_Babu_Samineni — Tue, 06 Feb 2018 00:38:11 GMT

We never ask data from live, but to interpret or decipher the some input atleast we need to look some data set even dump.

Re: MapSubstring, HTML, Clob, script load error no error message.

rwunderlich — Tue, 06 Feb 2018 03:48:06 GMT

I understand the idea of what you are trying to accomplish, but can't understand your script. Can you post at least one line of [PRLD Asset Long Desc] data to give us an idea? You can anonymize the contained data, I just want to understand the structure.

-Rob

Re: MapSubstring, HTML, Clob, script load error no error message.

Peter_Cammaert — Tue, 06 Feb 2018 08:30:21 GMT

Every [PRLD Asset Long Desc] value contains multiple HTML tags (e.g. according to the script "them things enclosed in < and >"). Using an ingenious (although not very efficient) trick, Philip maps every (because of the 2-parameter SubField() call) occurence of an HTML tag to an empty string and stores unique copies in a Mapping Table (after reattaching the < & > markers that were lost by calling TextBetween()). He then uses this same mapping table in a MapSubString() call to (try to) obliterate all HTML tags in each [PRLD Asset Long Desc] value.

I do apologise if I am explaining the obvious.

Re: MapSubstring, HTML, Clob, script load error no error message.

weephil76 — Tue, 06 Feb 2018 08:35:43 GMT

Yes Peter that's exactly it.

Problem is that the MapSubString() part ends the script, no error message or anything saying why it just terminates and then I get the usual script has failed do you wish to reload error. But as far as I can see the syntax is correct. I have an idea that the map table seems to contain two blank values so I'm going to test if that is an issue.

Re: MapSubstring, HTML, Clob, script load error no error message.

Peter_Cammaert — Tue, 06 Feb 2018 08:44:17 GMT

Let's assume that your script indeed runs until the end.

If you now add a STORE PRLDESC statement immediately after the LOAD of PRLDESC, what do you find in the PRLD_Resolved field?

Re: MapSubstring, HTML, Clob, script load error no error message.

rwunderlich — Tue, 06 Feb 2018 15:34:26 GMT

Thanks for the explanation. It wasn't obvious to me

Another alternative is to use a bit of macro code as a script function as shown in this example:

Search Recipes | Qlikview Cookbook

Re: MapSubstring, HTML, Clob, script load error no error message.

weephil76 — Tue, 06 Feb 2018 15:37:21 GMT

Hi Rob,

So that was my first attempt.

Didn't work when I scheduled a refresh so i guess there are either issues with macros in scheuled reports or some sort of config\settings required to get these to work.