26 April 2023 How to use MapSubstring 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! Mail and Deploy webinar Join our upcoming webinar and learn how Mail & Deploy helps you develop and deliver data-driven, high-impact reports from Qlik Cloud, Qlik Sense and QlikView. Test your Qlik knowledge! Check out the Friday Qlik Test Prep archive for Qlik certification questions and answers. 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 30 November 2023 Qlik Sense exploited in Cactus ransomware campaign We previously reported on patches for two critical vulnerabilities in Qlik Sense Enterprise for Windows. If you haven’t upgraded already, now is the time to take heed: these vulnerabilities are actively being exploited in the Cactus ransomware campaign. 26 October 2023 TimeXtender Job Scheduling with Qlik Application Automation Unlock the power of Qlik Application Automation to schedule TimeXtender jobs efficiently. Discover automation tips for seamless data workflows. Download the template and boost your productivity today! API Automation Qlik TimeXtender 21 September 2023 Qlik Sense Enterprise for Windows – Critical Security Fix #2 A new security issue in Qlik Sense Enterprise for Windows has been identified. This resolves an incomplete fix for the vulnerability that was reported earlier this month. Patches are available and it is recommended to update your Qlik Sense Enterprise for Windows environment at the earliest possibility. New Release Qlik Security
30 November 2023 Qlik Sense exploited in Cactus ransomware campaign We previously reported on patches for two critical vulnerabilities in Qlik Sense Enterprise for Windows. If you haven’t upgraded already, now is the time to take heed: these vulnerabilities are actively being exploited in the Cactus ransomware campaign.
26 October 2023 TimeXtender Job Scheduling with Qlik Application Automation Unlock the power of Qlik Application Automation to schedule TimeXtender jobs efficiently. Discover automation tips for seamless data workflows. Download the template and boost your productivity today! API Automation Qlik TimeXtender
21 September 2023 Qlik Sense Enterprise for Windows – Critical Security Fix #2 A new security issue in Qlik Sense Enterprise for Windows has been identified. This resolves an incomplete fix for the vulnerability that was reported earlier this month. Patches are available and it is recommended to update your Qlik Sense Enterprise for Windows environment at the earliest possibility. New Release Qlik Security