Extracting text from strings with the SubField() function 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 the SubField() function in Qlik Sense.

The answers were unanimous:

The correct answer is C

The SubField() function returns the n-th field of a string based on a delimiter. Consider the following string:

The quick brown fox jumps over the lazy dog

This string contains multiple words, separated by spaces (the delimiter). Let’s load this string to a field called Sentence:

Load * Inline [
    Sentence
    The quick brown fox jumps over the lazy dog
];

We can now return individual words from Sentence by using the SubField() function and specifying the field number. For example:

SubField(Sentence, ' ', 4)

Returns fox, the fourth word in the sentence, starting from the left of the string and using space as the delimiter.

We can use a negative number to retrieve part of the string from the right. For example:

SubField(Sentence, ' ', -2)

Returns lazy, the second word from the end of the string.

All in all, the SubField() function is very useful for quickly extracting parts of well-defined strings. It’s certainly easier than using nested combinations of Left(), Right(), Mid(), Len() or other Qlik text functions.

Some more examples of where SubField() might prove useful:

• Extracting first or last names from a field containing the full name;

• Extracting a domain name from an email address;

• Extracting an area code from a phone number;

• and much more.

Turning fields into rows with the SubField() function

In the examples above we’ve used the third argument to specify the field number to retrieve. This is an optional argument. If omitted, the SubField() function can be used to split a string into individual rows.

Consider the following table again:

Load * Inline [
    Sentence
    The quick brown fox jumps over the lazy dog
];

We can use the SubField() function to split the sentence into individual words like this:

Words:
Load
    SubField(Sentence, ' ') as Word
;
Load * Inline [
    Sentence
    The quick brown fox jumps over the lazy dog
];

The resulting Words table will contain the 9 rows, one for each word in the sentence.

Useful helper function: SubStringCount()

The SubStringCount() function is useful when you want to know how many delimited fields a string contains. It takes a string and the substring to search for. For example:

SubStringCount('The quick brown fox jumps over the lazy dog', ' ')

Will return 8.

“Why does it return 8 when the string contains 9 words?”

The SubStringCount() function returns 8 because it isn’t looking for the words, but for the delimiter. The sentence contains 8 spaces, between 9 words. It’s good to keep this in mind.

That’s it for this week, see you next Friday!