26 April 2022 Splitting strings with the Qlik SubField() function 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 Qlik SubField() function used to split strings based on a common delimiter: The correct answer is C: SubField() Without going in too to much depth of whether #HoursWorked is a measure we should be scared of or not, we guess this question hasn’t taken too much of your time this week, seeing how unanimously this has been answered. A transformation which is pretty commonly encountered is separating values from a single field. Whether it is a comma separated value, a composed field or retrieving information from a file location, it is often necessary to extract only part of a field value. For this question SubField() would be the syntax of choice. The power of SubField() lays in the fact that we separate the values based on a given delimiter, rather then a length. While in this case we could have simply said Right(‘$(vField’), 4) to retrieve the right four characters, this wasn’t part of the options, nor is it the most elegant. In many cases this won’t suffice, because the field we are trying to separate has a variable length or the whole string consists of more than two words. Qlik SubField() syntax The SubField() function has the following syntax: SubField(text, delimiter[, field_no ]) Reviewing the answer; our text is the variable vField which is split by a single space as the delimiter and we would like to return the second field number to retrieve only the year number from the string. Another thing to know about SubField() is that the field number we would like to return can be either a positive or a negative number. If we use a positive number, the return value would be the first field before the set delimiter and work its way subsequently backwards. If we use a negative number, the field value after the last delimiter would be returned and works its way subsequently forwards. To visualize: SubField(‘This is a test’, ‘ ‘, 3) returns ‘a’ SubField(‘This is a test’, ‘ ‘,-1) returns ‘test’ That’s it for this week. See you next Friday? More from the Bitmetric team Qlik Cloud Backup Protect your investment in Qlik with daily incremental backups stored in an encrypted environment with redundant storage. Available for as little as 2 Euro per day. Learn more. Masters Summit for Qlik The Masters Summit for Qlik provides the next step in your path to becoming a Qlik specialist. As an extra perk, Bitmetric has secured an over $450 discount just for you! Use code BITMETRIC at checkout. Read more here. Friday Qlik Test Prep Functions Qlik 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 12 May 2025 Dagelijkse Qlik Ondersteuning: altijd hulp bij data analyse Bij Bitmetric snappen we hoe belangrijk het is dat je Qlik omgeving gewoon goed werkt. Of je nu dagelijks data analyseert of snel wat inzichten nodig hebt, je wilt dat alles soepel verloopt. Daarom staan onze experts elke dag voor je klaar. Van kleine vragen tot ingewikkelde uitdagingen, we helpen je graag verder, zodat jij je kunt focussen op wat echt telt! Qlik 25 April 2025 Game-Changer in Qlik: Set Analysis Now Works WITHOUT Using It’s Syntax! Discover Qlik Cloud’s latest feature that lets you apply object level filters without writing any set analysis syntax. A simpler and faster way to build dashboards, especially for non-technical users. Read more in this blog post. New Release Qlik 23 April 2025 When Everyone Has Different Numbers: Why Data Alignment Matters Different teams, different data, different results. This post explores how misaligned data leads to confusion, and how TimeXtender helps bring everyone back to the same page. TimeXtender
12 May 2025 Dagelijkse Qlik Ondersteuning: altijd hulp bij data analyse Bij Bitmetric snappen we hoe belangrijk het is dat je Qlik omgeving gewoon goed werkt. Of je nu dagelijks data analyseert of snel wat inzichten nodig hebt, je wilt dat alles soepel verloopt. Daarom staan onze experts elke dag voor je klaar. Van kleine vragen tot ingewikkelde uitdagingen, we helpen je graag verder, zodat jij je kunt focussen op wat echt telt! Qlik
25 April 2025 Game-Changer in Qlik: Set Analysis Now Works WITHOUT Using It’s Syntax! Discover Qlik Cloud’s latest feature that lets you apply object level filters without writing any set analysis syntax. A simpler and faster way to build dashboards, especially for non-technical users. Read more in this blog post. New Release Qlik
23 April 2025 When Everyone Has Different Numbers: Why Data Alignment Matters Different teams, different data, different results. This post explores how misaligned data leads to confusion, and how TimeXtender helps bring everyone back to the same page. TimeXtender