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! More from the Bitmetric team 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! Enjoying these challenges? We have many more, and we’ll even pay you to solve them 😉 Check out our job opening. 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. Call us Mail us 25 April 2024 Over 3000 Qlik Sense servers still vulnerable to Cactus ransomware In November of last year we reported on how two critical vulnerabilities in Qlik Sense Enterprise for Windows were exploited in the Cactus ransomware campaign. As of today, over 3000 vulnerable Qlik Sense servers remain connected to the Internet. New Release Qlik Vulnerability 29 March 2024 Dealing with missing and late arriving values with OtherSymbol and other solutions Learn effective strategies for handling missing or late-arriving dimension values in Qlik with our expert guide. Discover how to use OtherSymbol, WHERE NOT EXISTS, and other solutions to deal with early-arriving facts Data Model Friday Qlik Test Prep Qlik Solution 22 March 2024 Picking the right color palette for your visualization Discover how to choose the perfect color palette for your data visualizations with our expert guide. Learn about single, categorical, sequential, and diverging color palettes to enhance your charts and graphs. Ideal for data analysts and visualizers. Friday Qlik Test Prep Qlik SenseTheme Visualization
25 April 2024 Over 3000 Qlik Sense servers still vulnerable to Cactus ransomware In November of last year we reported on how two critical vulnerabilities in Qlik Sense Enterprise for Windows were exploited in the Cactus ransomware campaign. As of today, over 3000 vulnerable Qlik Sense servers remain connected to the Internet. New Release Qlik Vulnerability
29 March 2024 Dealing with missing and late arriving values with OtherSymbol and other solutions Learn effective strategies for handling missing or late-arriving dimension values in Qlik with our expert guide. Discover how to use OtherSymbol, WHERE NOT EXISTS, and other solutions to deal with early-arriving facts Data Model Friday Qlik Test Prep Qlik Solution
22 March 2024 Picking the right color palette for your visualization Discover how to choose the perfect color palette for your data visualizations with our expert guide. Learn about single, categorical, sequential, and diverging color palettes to enhance your charts and graphs. Ideal for data analysts and visualizers. Friday Qlik Test Prep Qlik SenseTheme Visualization