6 April 2023

Extracting text from strings with the SubField() function in Qlik Sense

Share this message
qlik sense subfield

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.

Extract parts of a string with 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!

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!

Do you want to work within a highly-skilled, informal team where craftsmanship, ingenuity, knowledge sharing and personal development are valued and encouraged? Check out our job openings.

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.