29 June 2022 How to use the Lookup() function 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 the Lookup() function in Qlik: The correct answer is C: Roller, UMW, Cobretti, Grotti Most of you got this answer right and that is no surprise. Things like vertical lookup, horizontal lookup and maybe even index match must be very familiar topics for many, but why still use Excel when we have Qlik, right? The lookup() function will evaluate a field from the current or a previously loaded table and find the matching field you enter. Or to put it as the syntax: lookup(field_name, match_field_name, match_field_value [, table_name]) To evaluate the right answer let’s visualize what lookup() has exactly done in the figure below: The Lookup() function is told to find the field value of ‘Manufacturer’ in the target table and match this to the field value of ‘ManufacturerID’ in the target table and ‘ManufacturerID’ in the current table. In the last part of the syntax we determine the target table which in this example is ‘ManufacturerData’. After the match is made it is added to the current table as the column ‘Manufacturer’. Please notice that all the input which refer to the target table are given in quotes. This is mandatory since the arguments without quotes refer to the current table. In this example we also immediately notice some of the limitations of lookup(). When Lookup() is being used, it will always return the value based on the load order and if no match is found it will return NULL. So in this example we are missing ManufacturerID = 4, so it returns NULL and since ManufacturerID = 5 is duplicated, it will return Grotti, since that value is loaded first. And hopefully this is the moment many of you hear the alarm bells ring about data quality. There are quite some things to fix in this data model, we agree, and probably lookup shouldn’t even be used here. Other options like ApplyMap() or even a join could just as well be used and are maybe even better, since the performance of lookup() is not known to be the fastest. So why use lookup? The main reason of this example is not to only show the disadvantages of lookup() but also bring us to the advantages. The strength of lookup() doesn’t lie in applying the lookup to whole tables, but more to get single values from a table, for example as use in variables: LET vExcludeManufacturer =Lookup('Manufacturer', 'ManufacturerID', 1, 'ManufacturerData'); LET vExcludeManufacturerID =Lookup('ManufacturerID', 'Manufacturer', 'Roller' , 'ManufacturerData'); These will result in: vExcludeManufacturer = Roller vExcludeManufacturerID = 1 And while this is an example in a very small dataset, the use case for large datasets is quite clear. So to cut it short, while ApplyMap() or Join would be the better and most of the times faster solutions to join fields to another table, Lookup() is a great way to quickly find individual values from a table, for example to use in a variable. 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! 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 Functions Performance 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