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 Qlik Cloud Backup Protect your investment in Qlik with daily incremental backups stored in an encrypted environment with redundant storage. Available for as little as 2 Euro per day. Learn more. Masters Summit for Qlik The Masters Summit for Qlik provides the next step in your path to becoming a Qlik specialist. As an extra perk, Bitmetric has secured an over $450 discount just for you! Use code BITMETRIC at checkout. Read more here. 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 12 May 2025 Dagelijkse Qlik Ondersteuning: altijd hulp bij data analyse Bij Bitmetric snappen we hoe belangrijk het is dat je Qlik omgeving gewoon goed werkt. Of je nu dagelijks data analyseert of snel wat inzichten nodig hebt, je wilt dat alles soepel verloopt. Daarom staan onze experts elke dag voor je klaar. Van kleine vragen tot ingewikkelde uitdagingen, we helpen je graag verder, zodat jij je kunt focussen op wat echt telt! Qlik 25 April 2025 Game-Changer in Qlik: Set Analysis Now Works WITHOUT Using It’s Syntax! Discover Qlik Cloud’s latest feature that lets you apply object level filters without writing any set analysis syntax. A simpler and faster way to build dashboards, especially for non-technical users. Read more in this blog post. New Release Qlik 23 April 2025 When Everyone Has Different Numbers: Why Data Alignment Matters Different teams, different data, different results. This post explores how misaligned data leads to confusion, and how TimeXtender helps bring everyone back to the same page. TimeXtender
12 May 2025 Dagelijkse Qlik Ondersteuning: altijd hulp bij data analyse Bij Bitmetric snappen we hoe belangrijk het is dat je Qlik omgeving gewoon goed werkt. Of je nu dagelijks data analyseert of snel wat inzichten nodig hebt, je wilt dat alles soepel verloopt. Daarom staan onze experts elke dag voor je klaar. Van kleine vragen tot ingewikkelde uitdagingen, we helpen je graag verder, zodat jij je kunt focussen op wat echt telt! Qlik
25 April 2025 Game-Changer in Qlik: Set Analysis Now Works WITHOUT Using It’s Syntax! Discover Qlik Cloud’s latest feature that lets you apply object level filters without writing any set analysis syntax. A simpler and faster way to build dashboards, especially for non-technical users. Read more in this blog post. New Release Qlik
23 April 2025 When Everyone Has Different Numbers: Why Data Alignment Matters Different teams, different data, different results. This post explores how misaligned data leads to confusion, and how TimeXtender helps bring everyone back to the same page. TimeXtender