24 August 2022 Cleaning data in Qlik with MAP … USING Deel dit bericht 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 cleaning data in Qlik: The correct answer is B: Map … Using … Of course the obvious discrepancy in the data being loaded in this example was clear to everyone. There are not only three different ways in which the United States is being loaded, but Holland and The Netherlands are also (really) one and the same country. According to the question the solution should be found in the load script and we are looking for the best solution. The best solution in this case being the answer the Qlik certification exam wants to hear: map using. However since this is by no means a bad solution and could very well be applicable to certain applications, ApplyMap() is also commonly used for ease of use and understanding in the script. So in perspective of maintainability of the script it might even be the better solution. In the example we have several fields over multiple tables which have the same data quality issues. Therefor the best fix would be to use map using to cover all fields with a single function. Let’s dive in and see how this works. First of we will need a mapping table. In the example below we have created an inline table, but in most real life scenario’s an attached or stored excel file with the mappings would be the solution to go for. A mapping table is a table consisting of two fields. The first field being the value we are looking for and the second being the new value we would like to have it replaced with. The load or select statement is being prefixed with mapping to tell Qlik this is a mapping table. A mapping table is automatically dropped at the end of the load. In the mapping table being loaded in this example, we change all values to either ‘The Netherlands’ or ‘ The United States’. Next up is to tell Qlik which fields we would like to change the values of and with the use of which mapping. This is done by map using. Please notice, that it is important to use this function before the tables are loaded. Qlik will evaluate map using after loading the table, but before storing. So before loading the different dimension tables in the example we add the following line to the script: This then results in Qlik evaluating the values of these fields before storing the table and applying when necessary, resulting in a nice and cleansed data model: Some other things to notice: Unmap The map function will keep running until the end of the script. This is stopped by using the unmap statement. But notice that the unmap syntax is unmap (fieldlist). So you have to specifically name the fields you want to stop being mapped, not the mapping table being used. You can also use a wildcard (*) to stop all fields from being mapped. ApplyMap Map using will only evaluate and change the value after loading the table and while it is being stored. If you want to make changes on an expression level it is better to use ApplyMap(), since with this function you have the option to give a default value if nothing is found. The syntax is as following: ApplyMap('map_name', expression [ , default_mapping ] ) If you leave the default mapping empty, the results are the same as with map using. However, pay attention that if we use a default mapping with the mapping table we made in this example, the values for Belgium, Germany and France will be replaced by the default value. Let’s take the following expression for example: ApplyMap('MAP_Country', Country, 'No value') AS Country In map using Qlik will only change the values from the mapping table, but leave all others in place, since the whole table has already been loaded. However, ApplyMap() is already evaluating during the load of each row and it will replace values it cannot find by the default option. Looking back at the mapping table, we don’t have values for Belgium, Germany and France, so these will be loaded as ‘No value’. That’s it for this week. See you next Friday? 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 Script Solution Hoe kunnen we je ondersteunen? Barry beschikt over meer dan 20 jaar ervaring als architect, developer, trainer en auteur op het gebied van Data & Analytics. Hij is bereid om je te helpen met al je vragen. Bel ons Mail ons 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