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! 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 8 October 2024 Artificial Intelligence, Machine Learning, and Deep Learning Explained: How They Impact Your Business In today’s rapidly evolving technological landscape, Artificial Intelligence (AI), Machine Learning (ML), and Deep Learning (DL) are transforming industries and redefining how businesses operate. In this blog post, we will break down these three definitions and elaborate on them. AI 25 September 2024 Building Ethical AI: Practical Frameworks for Responsible Innovation AI is transforming industries with innovation and efficiency. But with great power comes great responsibility. The real question is: How do you turn ethical principles into actionable guidelines for AI development? And what steps should your team take to make it happen? AI 17 September 2024 What is AI Ready Data Data quality is all about how accurate, consistent, complete, and up-to-date your data is. If your data is good, you’ll get reliable insights and be able to make smarter decisions. It’s a key part of making sure your AI and machine learning projects are successful. AI Qlik
8 October 2024 Artificial Intelligence, Machine Learning, and Deep Learning Explained: How They Impact Your Business In today’s rapidly evolving technological landscape, Artificial Intelligence (AI), Machine Learning (ML), and Deep Learning (DL) are transforming industries and redefining how businesses operate. In this blog post, we will break down these three definitions and elaborate on them. AI
25 September 2024 Building Ethical AI: Practical Frameworks for Responsible Innovation AI is transforming industries with innovation and efficiency. But with great power comes great responsibility. The real question is: How do you turn ethical principles into actionable guidelines for AI development? And what steps should your team take to make it happen? AI
17 September 2024 What is AI Ready Data Data quality is all about how accurate, consistent, complete, and up-to-date your data is. If your data is good, you’ll get reliable insights and be able to make smarter decisions. It’s a key part of making sure your AI and machine learning projects are successful. AI Qlik