How to use text functions in Qlik Sense?

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:

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 brake the description as well. So let’s have a look at each answer and why or why not to use it.

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. And remember:

Are these questions too easy for you?

If these questions are too easy for you, then you may be ready for the Masters Summit for Qlik.

At the Masters Summit for Qlik, we take your Qlik skills to the next level in 3 days with deep dives about:

• Qlik Data Modelling
• Qlik Scripting
• Set Analysis and Advanced Aggregations
• Solution Architecture & Performance
• Effective Visualizations
• Performance Tuning
• Qlik Sense integration

The courses are taught by Qlik veterans Rob Wunderlich, Oleg Troyanksy, Bill Lay, Nick Webster and Bitmetric’s Barry Harmsen. Together, this team brings almost 75 years of Qlik experience and has implemented hundreds of successful projects.

You can find more information and registration on the Masters Summit for Qlik website.

See you next week!

• If you have suggestions for questions, we love to hear from you via WhatsApp or at info@bitmetric.nl
• If you’re enjoying these questions and want to work on stuff like this every day (but a bit more challenging), we’re always on the lookout for new colleagues. Check our job openings here.
• For more of these brain teasers, check out our archive of Qlik certification questions and answers.
• Want to stay up to date about everything Qlik? Then sign up for our newsletter below: