14 December 2022 Using ApplyMap in Qlik Sense 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 using ApplyMap in Qlik Sense. Great to see so many responses and correct answers again. Luckily ApplyMap seems to be a very familiar function. The correct answer is D. What is ApplyMap? ApplyMap is a powerful function in Qlik Sense, making it possible to associate certain values with the contents of a field. In this question ApplyMap is used to take the values of the field ‘Country Name‘ and associate those with the field Country from another table. The Syntax for ApplyMap is as follows: ApplyMap('map_name', expression [ , default_mapping ] ) The Mapping Table: First off in order to properly use the ApplyMap function you need something called a mapping table. This table consists of two fields, the first containing the values you are comparing, the second containing the mapping values. As in the question we have the following table: CountryCodeCountry NameSESwedenJPJapanMXMexico It is good to understand that this table only consists in the internal memory of Qlik. After running the Load Script all mapping tables are automatically dropped from the data model. A mapping table is created by using the prefix Mapping to a load or select statement. For example, the mapping table above was loaded as: MAP_CountryCode:MAPPING LOAD [Countrycode], [Country Name]FROM [Lib://Countries.QVD](qvd); It is important to properly name the mapping table, since this is the identifier for the ApplyMap function. And yes! There are more options to use a Mapping Table with. The Map….Using and Rename Field statements (read more about Map…Using here) or the Mapsubstring function. However let’s focus in this case only on ApplyMap: Using the Mapping Table with ApplyMap The syntax for ApplyMap consists out of three input variables. The map_name, which is the name of the Mapping Table you are referring to. The expression or field you want to have compared and a default mapping. Let’s have another look at the ApplyMap syntax below, this time compared to how it was used in this week’s question: ApplyMap('map_name', expression [ , default_mapping ] )ApplyMap(‘MAP_CountryCode’, Country) AS [Country Name] We see in the ApplyMap syntax that the default mapping is optional and in the expression used in the question, the default mapping was left empty. This means that the already existing value of the evaluated field will remain. So in this case there is no match between ‘IN’ and the mapping table, resulting in the result of answer D. Now if we change the expression to include the default mapping as follows: ApplyMap(‘MAP_CountryCode’, Country, ‘Country Unknown’) AS [Country Name] This will return ‘Country Unknown’ when the field value ‘IN’ is evaluated. Making it possible to select this in the front-end and use this to fix data quality issues. Benefits of ApplyMap While loading the Fact table from this question we see that the Country field are country short codes. This is something unwanted in the front end. Therefor this needs to be replaced with the country names as found in the Countries QVD. Simple right? Just use a left join and presto: All countries are matched. However. This means that the fact table still contains the field Country which is the short code. This might not only be confusing; having Country and Country Name together in a single model. It is redundant information. So the field has to be dropped. And what if the fact table is build up from many different tables? We might need to do more joins in the end… This is all negated by using the ApplyMap function and a Mapping Table. Since the table is only loaded once and automatically dropped at the end of the script, there is not much overhead. It is simple to use and easy to understand. Even better, it will help with data quality. As we saw that values which are not mapped can be given a default value. 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