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! More from the Bitmetric team Qlik Cloud Backup Protect your investment in Qlik with daily incremental backups stored in an encrypted environment with redundant storage. Available for as little as 2 Euro per day. Learn more. Join the team! Do you want to work within a highly-skilled, informal team where craftsmanship, ingenuity, knowledge sharing and personal development are valued and encouraged? Check out our job openings. Friday Qlik Test Prep Solution How can we help? Barry has over 20 years experience as a Data & Analytics architect, developer, trainer and author. He will gladly help you with any questions you may have. Call us Mail us 16 April 2025 The Cost of Bad Data: What Is It Really Doing to Your Business? Inaccurate or outdated data doesn’t just cause small hiccups. This can severely impact your bottom line. It slows down your teams, leads to expensive errors, and creates serious compliance risks. The good news is that these challenges are avoidable. TimeXtender 3 April 2025 Rethinking Pop-Ups in Qlik Cloud: A Simple Yet Effective Solution Struggling with the lack of native pop up support in Qlik Cloud? Learn how we created a smooth and user friendly alternative using guided sheet transitions with only native Qlik functionality. No extensions or layout issues involved. Qlik Solution Visualization 10 March 2025 Qlik Data Flow: Simplifying Data Transformation Without Code Qlik Data Flow simplifies data transformation with a visual editor. Clean, join, and reshape data using drag and drop, no coding needed. Automatically generate Qlik script as you build. Learn how it works, see a step-by-step example, and compare it to Qlik Data Manager and Qlik Script. ETL No-Code Qlik Script
16 April 2025 The Cost of Bad Data: What Is It Really Doing to Your Business? Inaccurate or outdated data doesn’t just cause small hiccups. This can severely impact your bottom line. It slows down your teams, leads to expensive errors, and creates serious compliance risks. The good news is that these challenges are avoidable. TimeXtender
3 April 2025 Rethinking Pop-Ups in Qlik Cloud: A Simple Yet Effective Solution Struggling with the lack of native pop up support in Qlik Cloud? Learn how we created a smooth and user friendly alternative using guided sheet transitions with only native Qlik functionality. No extensions or layout issues involved. Qlik Solution Visualization
10 March 2025 Qlik Data Flow: Simplifying Data Transformation Without Code Qlik Data Flow simplifies data transformation with a visual editor. Clean, join, and reshape data using drag and drop, no coding needed. Automatically generate Qlik script as you build. Learn how it works, see a step-by-step example, and compare it to Qlik Data Manager and Qlik Script. ETL No-Code Qlik Script