How to use MapSubstring

Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question about how to use SubMapstring:

Interesting to see we got unanimously the same answer and although it definitely is part of the answer, TextBetween by itself will not produce the correct results! It is Answer C we were looking for; MapSubstring.

The problem explained

While TextBetween is a very useful function, closer examination of the data reveals that there are a few problems with it. The tomato names have a <strong> tag, making them bold in the front end. But it is this tag that messes up the TextBetween function. We could argue that using

TextBetween(HTMLDATA, '>', '<', 3) & ' ' & TextBetween(HTMLDATA, '>', '<', 4)

would result in the correct results, giving us respectively the string between the strong tags and the strong and data tag. However there is also a header named tomatoes we might want to retrieve, to categorize the data with. And what happens if a tomato has not received the strong tag? The function would deliver undesirable results, since it is not dynamic.

Making it dynamic

To tackle this problem it is the smartest way to remove all HTML tags dynamically. This is done by using MapSubstring. The MapSubstring function makes it possible to apply mappings to certain parts of a string, not the whole field. In this case we could make a mapping table of all HTML tags and substitute them with an empty value. Thus removing all HTML tags when used.

In order to do this we do start off with the TextBetween function. So your answers weren’t as wrong as we might have made them out to be.

The solution

As explained in the previous paragraph the solution is to create a mapping table with HTML tags and empty replacements. This is done as following:

MAP_StripHTMLTags
MAPPING LOAD
‘<‘ & TextBetween(‘<‘ & SubField(HTMLDATA, ‘<‘), ‘<‘, ‘>’) & ‘>’ AS HTMLTag
” AS ReplaceValue
RESIDENT TempTable;

First we create the mapping table. As we can see Answer D: SubField is also sneakily used here. What happens in this mapping table? We use the SubField function to retrieve the string after a ‘<‘. So it returns all strings starting from a ‘<‘. Next we use the TextBetween function. We tell this function to retrieve all text between ‘<‘ and ‘>’. To do this we add back the ‘<‘ before the SubField function, so we retrieve all information in between the angled brackets, since otherwise the starting bracket is gone. This gives us a clean list of all used HTML tags:

All what rests us to do is to add the angled brackets again to create the HTML tag. Resulting in the following string:

<' & TextBetween('<' & SubField(HTMLDATA, '<'), '<', '>') & '>

MapSubstring

Fact:
LOAD
RecNo() AS RecordID
MapSubstring(‘MAP_StripHTMLTags’, HTMLDATA) AS Products
RESIDENT TempTable;
Drop Table TempTable:

To finalize the result we use MapSubstring. The basics of the function are the same as an ApplyMap, but now it is done on parts of the string in the field, instead if the whole string. In this case it will evaluate all HTML tags in the string and replace those with the mapping. In this case the mapping being an empty value. So this is how you remove all HMTL tags dynamically.

That’s it for this week!

More from the Bitmetric team

Bitmetric Qlik Support

Keep your Qlik environment running smoothly with proactive support that prevents issues before they appear. Available on flexible monthly plans. Learn more.

Qlik vs Power BI Series

See how Qlik and Power BI perform when theory meets reality. Three apps rebuilt, every step examined. Learn more.