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

[weephil76]

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

[Peter_Cammaert]

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

[weephil76]

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? 

[Anil_Babu_Samineni]

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

[weephil76]

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

[Anil_Babu_Samineni]

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

[rwunderlich]

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

[Peter_Cammaert]

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.

[weephil76]

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.

[Peter_Cammaert]

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?