String Functions: Part 2

It's been a while since my last posting; this is what happens when life and work priorities inundate us. None-the-less, I am excited to continue this series and explore string functions in greater detail.

I've always believed that the greatest challenge for any developer is being able to apply ingenuity with pre-built functions. Taking the shortest route possible to an answer and in the end, creating a clean and eloquent solution in its approach and performance.

Today we will be looking at Subfield(), a much loved function in the QlikCommunity

Subfield(s, 'delimiter' [ , index ]) - The help file explains this best :)

In its three-parameter version, this script function returns a given substring from a larger string s with delimiter 'delimiter'. Index is an optional integer denoting which of the substrings should be returned. If index is omitted when subfield is used in a field expression in a load statement, the subfield function will cause the load statement to automatically generate one full record of input data for each substring that can be found in s. 

It is a very powerful function that allows you to either create a record for every instance encountered through the delimiter (Cartesian Join) or a sub-string when a particular instance is indicated through the index parameter.

Lets look at both its 2 and 3 parameter output

The 3 parameter output gives us our Full Name split into First Name and Surname. This allows us on the front-end to provide the end user with the option of now selecting how they want to find the person in question. 

A short-cut to this without scripting is utilising the 'tree-view' in the list box properties.Tree view based on field FullName using ',' as delimiter

2 parameter output gives us the Cartesian join on our field FullName.

The subfield function can be used at different stages within your load script. For instance, you can use it in your where clause to load a reduced dataset based on a condition that is met with your subfield() expression.

Happy Qliking


Cheers,
Byron