17 August 2022 Using text functions 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 text functions in Qlik Sense: The correct answer is A: Mid() As data architect it is a quite common occurrence that certain modifications have to be made to fields. Whether it is to improve readability or to increase filtering and selection possibilities, composite fields need to be dealt with. Luckily Qlik provides us with a nice arsenal of functions to help with this, each with its own advantages and disadvantages. We will have a look at the ones mentioned in the question, however it is good to know, there are even more then we have shown in the question. In the question the answer lies in the structure of the string. The string is made up of a code and a description separated by a dot. A lot of answers we have received were Answer B: SubField(). However, since the description in some cases also contained a dot, the SubField function would break the description as well. So let’s have a look at each answer and why or why not to use it. The answer table Answer A – Mid(String, 5): Since in the example the preceding code is all of the same length, we assume this is the case in the whole data model. Therefor we use Mid() to give a starting position from which to keep the string. In this case the starting position we want is five, since we have a three character code and a dot, which makes the total preceding code length four. By starting from position five we drop the four preceding characters. The syntax of mid also gives the possibility to limit the amount of characters after the starting position. By leaving this blank, we take the whole string. Mid(text, start[, count]) Answer B – SubField(String, ‘.’, 2) The most common wrong answer was SubField. A great syntax to split strings in various ways and also to determine which separated part to keep. However in this case the character on which to split (the dot), is not unique, but also appears later on in the string, creating multiple subfields and thus leaving an incomplete description. For more information about SubField, we have written about this syntax before. SubField(text, delimiter[, field_no ]) Answers C & D- Left(String, 5) & Right(String, 5) Where Mid() gives a starting position and an amount of characters, Left() and Right() are doing the same thing, but then, as the name implies, already starting all the way to the left or right of the string. The number in the syntax is the amount of characters we would like to keep. Since the string is of variable length, there is no way by solely using these syntaxes to get the full description. Left(text, count)Right(text, count) Answer E – Trim(String) All that Trim does is remove leading and trailing spaces. In certain cases a very handy tool, not in this example however. Trim(text) Answer F – FindOneOf(String, ‘.’) FindOneOf() will look up the given position for the given character. In this case it will look in the field values of String for the dot. The return is always the position number on which it was first found. It is also possible to lookup for the n-th position. For example, FindOneOf(String, ‘.’, 2) will look for the second dot and returns that position value. FindOneOf(text, char_set[, count]) Answer G – PurgeChar(String, ‘.’) PurgeChar() does exactly what is name inscribes. It removes the given characters from the given string. In this case we ask it to remove dot from the field String. Ending in the completely unwanted result of Answer G. PurgeChar(text, remove_chars) An honorable mention: Answer H – TextBetween(String, ‘.’, ”) Not an answer in the question, but definitely a great solution as well. The TextBetween syntax creates a very dynamic way of retrieving certain strings. In this case it will return the text between the dot and nothing, meaning it will return everything until the end of the string. TextBetween(text, delimiter1, delimiter2[, n]) 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 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 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 February 2024 What’s New inĀ Qlik SenseĀ February 2024 for Administrators This Qlik Sense February 2024 release providesĀ Qlik SenseĀ administrators a summary of the features and improvements available. New Release Qlik
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 February 2024 What’s New inĀ Qlik SenseĀ February 2024 for Administrators This Qlik Sense February 2024 release providesĀ Qlik SenseĀ administrators a summary of the features and improvements available. New Release Qlik