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! Mail and Deploy webinar Join our upcoming webinar and learn how Mail & Deploy helps you develop and deliver data-driven, high-impact reports from Qlik Cloud, Qlik Sense and QlikView. Test your Qlik knowledge! Check out the Friday Qlik Test Prep archive for Qlik certification 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 30 November 2023 Qlik Sense exploited in Cactus ransomware campaign We previously reported on patches for two critical vulnerabilities in Qlik Sense Enterprise for Windows. If you haven’t upgraded already, now is the time to take heed: these vulnerabilities are actively being exploited in the Cactus ransomware campaign. 26 October 2023 TimeXtender Job Scheduling with Qlik Application Automation Unlock the power of Qlik Application Automation to schedule TimeXtender jobs efficiently. Discover automation tips for seamless data workflows. Download the template and boost your productivity today! API Automation Qlik TimeXtender 21 September 2023 Qlik Sense Enterprise for Windows – Critical Security Fix #2 A new security issue in Qlik Sense Enterprise for Windows has been identified. This resolves an incomplete fix for the vulnerability that was reported earlier this month. Patches are available and it is recommended to update your Qlik Sense Enterprise for Windows environment at the earliest possibility. New Release Qlik Security
30 November 2023 Qlik Sense exploited in Cactus ransomware campaign We previously reported on patches for two critical vulnerabilities in Qlik Sense Enterprise for Windows. If you haven’t upgraded already, now is the time to take heed: these vulnerabilities are actively being exploited in the Cactus ransomware campaign.
26 October 2023 TimeXtender Job Scheduling with Qlik Application Automation Unlock the power of Qlik Application Automation to schedule TimeXtender jobs efficiently. Discover automation tips for seamless data workflows. Download the template and boost your productivity today! API Automation Qlik TimeXtender
21 September 2023 Qlik Sense Enterprise for Windows – Critical Security Fix #2 A new security issue in Qlik Sense Enterprise for Windows has been identified. This resolves an incomplete fix for the vulnerability that was reported earlier this month. Patches are available and it is recommended to update your Qlik Sense Enterprise for Windows environment at the earliest possibility. New Release Qlik Security