26 April 2023 How to use SubMapstring Share this message 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_StripHTMLTagsMAPPING LOAD‘<‘ & TextBetween(‘<‘ & SubField(HTMLDATA, ‘<‘), ‘<‘, ‘>’) & ‘>’ AS HTMLTag” AS ReplaceValueRESIDENT 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:LOADRecNo() AS RecordIDMapSubstring(‘MAP_StripHTMLTags’, HTMLDATA) AS ProductsRESIDENT 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! Friday Qlik Test Prep Solution How can we help? Feel free to contact us if you have any comments or questions. Call us Mail us 23 May 2023 What’s New in Qlik Sense May 2023 for Administrators This blog post provides Qlik Sense administrators a summary of the new administrative features and improvements available in Qlik Sense Enterprise on Windows. Let’s get started on what’s new in Qlik Sense for May 2023. New Release Qlik 23 May 2023 What’s New in Qlik Sense May 2023 for Business Users, Analytic Creators and Data Integrators This blog post provides Qlik Sense business users, analytic creators, and data integrators a summary of the features and improvements available in Qlik Sense Enterprise on Windows. Let’s get started with what’s new in Qlik Sense for May 2023. New Release Qlik 11 May 2023 How to make seasonal trendlines in Qlik Sense 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 make seasonal trendlines: The correct answer was B! Trend analysis Qlik has added Time series decomposition modifier functions to the line chart. This can […] Friday Qlik Test Prep Solution
23 May 2023 What’s New in Qlik Sense May 2023 for Administrators This blog post provides Qlik Sense administrators a summary of the new administrative features and improvements available in Qlik Sense Enterprise on Windows. Let’s get started on what’s new in Qlik Sense for May 2023. New Release Qlik
23 May 2023 What’s New in Qlik Sense May 2023 for Business Users, Analytic Creators and Data Integrators This blog post provides Qlik Sense business users, analytic creators, and data integrators a summary of the features and improvements available in Qlik Sense Enterprise on Windows. Let’s get started with what’s new in Qlik Sense for May 2023. New Release Qlik
11 May 2023 How to make seasonal trendlines in Qlik Sense 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 make seasonal trendlines: The correct answer was B! Trend analysis Qlik has added Time series decomposition modifier functions to the line chart. This can […] Friday Qlik Test Prep Solution