6 April 2023 Extracting text from strings with the SubField() function 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 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! See you at QlikWorld 2023 in Vegas? Come see us at QlikWorld 2023 for a chance to win a free, lifetime SenseTheme subscription and get some cool swag. Want more Friday Qlik Test Prep? Check out the Friday Qlik Test Prep archive for more Qlik questions and answers. Friday Qlik Test Prep Functions Solution How can we help? Feel free to contact us if you have any comments or questions. Call us Mail us 23 May 2023 What’s New in Qlik Sense May 2023 for Administrators This blog post provides Qlik Sense administrators a summary of the new administrative features and improvements available in Qlik Sense Enterprise on Windows. Let’s get started on what’s new in Qlik Sense for May 2023. New Release Qlik 23 May 2023 What’s New in Qlik Sense May 2023 for Business Users, Analytic Creators and Data Integrators This blog post provides Qlik Sense business users, analytic creators, and data integrators a summary of the features and improvements available in Qlik Sense Enterprise on Windows. Let’s get started with what’s new in Qlik Sense for May 2023. New Release Qlik 11 May 2023 How to make seasonal trendlines 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 how to make seasonal trendlines: The correct answer was B! Trend analysis Qlik has added Time series decomposition modifier functions to the line chart. This can […] Friday Qlik Test Prep Solution
23 May 2023 What’s New in Qlik Sense May 2023 for Administrators This blog post provides Qlik Sense administrators a summary of the new administrative features and improvements available in Qlik Sense Enterprise on Windows. Let’s get started on what’s new in Qlik Sense for May 2023. New Release Qlik
23 May 2023 What’s New in Qlik Sense May 2023 for Business Users, Analytic Creators and Data Integrators This blog post provides Qlik Sense business users, analytic creators, and data integrators a summary of the features and improvements available in Qlik Sense Enterprise on Windows. Let’s get started with what’s new in Qlik Sense for May 2023. New Release Qlik
11 May 2023 How to make seasonal trendlines 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 how to make seasonal trendlines: The correct answer was B! Trend analysis Qlik has added Time series decomposition modifier functions to the line chart. This can […] Friday Qlik Test Prep Solution