Using 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 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.

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?