Bitmetric Friday Qlik Test Prep – Week 16 – SubField()

By Bitmetric Admin

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:

Geen alternatieve tekst opgegeven voor deze afbeelding

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.

The SubField syntax is used as following:

 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) would return ‘a’

Geen alternatieve tekst opgegeven voor deze afbeelding

SubField(‘This is a test’, ‘ ‘,-1) would return ‘test’

Geen alternatieve tekst opgegeven voor deze afbeelding

  That’s it for this week. See you next Friday? And remember:

  • If you have suggestions for questions, we love to hear from you via WhatsApp or at [email protected]
  • If you’re enjoying these questions and want to work on stuff like this every day (but a bit more challenging), we’re always on the lookout for new colleagues. Check our job openings here.

Previous posts

Week 15: FirstSortedValue()

Week 14: Date() vs Date#()

Week 13: Set Analysis – Literal vs Search Strings

Week 12: Automatic Concatenation

Week 11: Sum(TOTAL)

Week 10: Unpivoting data

Week 9: Statements & Breakpoints

Week 8: Sales & Budget model

Week 7: MonthEnd(Today())

Week 6: Looping Tables

Week 5: Set Identifiers

Week 4: Time series visualization

Week 3: Circular References & Synthetic Keys

Week 2: Section Access

Week 1: Optimized Load