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 Take your Qlik skills to the next level! Since 2013, the Masters Summit for Qlik is the premier advanced training for Qlik. Join us in Vienna and take your Qlik skills to the next level. Join the team! Enjoying these challenges? We have many more, and we’ll even pay you to solve them 😉 Check out our job opening. 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 29 March 2024 Dealing with missing and late arriving values with OtherSymbol and other solutions Learn effective strategies for handling missing or late-arriving dimension values in Qlik with our expert guide. Discover how to use OtherSymbol, WHERE NOT EXISTS, and other solutions to deal with early-arriving facts Data Model Friday Qlik Test Prep Qlik Solution 22 March 2024 Picking the right color palette for your visualization Discover how to choose the perfect color palette for your data visualizations with our expert guide. Learn about single, categorical, sequential, and diverging color palettes to enhance your charts and graphs. Ideal for data analysts and visualizers. Friday Qlik Test Prep Qlik SenseTheme Visualization 11 March 2024 5 ways to pass selections between Alternate States in Qlik Sense This guide delves into the intricacies of passing selections between Alternate States in Qlik Sense, offering a comprehensive look at techniques such as explicit and implicit assignments, merging selections, and utilizing granularity. Perfect for developers and data analysts, it equips you with the knowledge to leverage Alternate States effectively, enhancing dashboard functionality and enriching data analysis. Expressions Friday Qlik Test Prep Qlik
29 March 2024 Dealing with missing and late arriving values with OtherSymbol and other solutions Learn effective strategies for handling missing or late-arriving dimension values in Qlik with our expert guide. Discover how to use OtherSymbol, WHERE NOT EXISTS, and other solutions to deal with early-arriving facts Data Model Friday Qlik Test Prep Qlik Solution
22 March 2024 Picking the right color palette for your visualization Discover how to choose the perfect color palette for your data visualizations with our expert guide. Learn about single, categorical, sequential, and diverging color palettes to enhance your charts and graphs. Ideal for data analysts and visualizers. Friday Qlik Test Prep Qlik SenseTheme Visualization
11 March 2024 5 ways to pass selections between Alternate States in Qlik Sense This guide delves into the intricacies of passing selections between Alternate States in Qlik Sense, offering a comprehensive look at techniques such as explicit and implicit assignments, merging selections, and utilizing granularity. Perfect for developers and data analysts, it equips you with the knowledge to leverage Alternate States effectively, enhancing dashboard functionality and enriching data analysis. Expressions Friday Qlik Test Prep Qlik