Qlikview Subfield Function

This post demonstrates the use of the Qlikview Subfield Function.  For my use case, I am loading an Inline table with sample column data, then creating a second table with sample Qlikview Subfield Function use cases, using the LOAD Resident technique.

Qlikview Version:  Personal Edition 12.0.20100.0 SR2 64-bit Edition (x64)

OS:  Windows 10

From Qlik Help:  Subfield() is used to extract substring components from a parent string field, where the original record fields consist of two or more parts separated by a delimiter.  The Subfield() function can be used, for example, to extract first name and surname from a list of records consisting of full names, the component parts of a path name, or for extracting data from comma-separated tables.  If you use the Subfield() function in a LOAD statement with the optional field_no parameter left out, one full record will be generated for each substring. If several fields are loaded using Subfield() the Cartesian products of all combinations are created.

Syntax:  SubField(text, delimiter[, field_no ])

Setup – Qlikview Subfield Function

First, setup a new QVW and load the following sample data:

To view the results, setup a Table Box and add the fields:

  • Name – This is your raw, unformatted columns
  • Name1 – This shows impact of function Subfield(Name,’-‘,1), which takes the first substring in original field, separated by delimiter “-“
  • Name2 – This shows impact of function Subfield(Name,’-‘,2), which takes the second substring in original field, separated by delimiter “-“
  • Name3 – This shows impact of function Subfield(Name,’-‘,3), which takes the third substring in original field, separated by delimiter “-“

Set sort order by field Name, Load-Original.

I added the function text as captions for Name1, Name2, and Name3.

Resulting Table Box will look like this:

Qlikview Subfield Function

 

A couple observations:

  • For Name “Bills-123”, the third function returns a NULL string, since there are only two substrings separated by delimiter “-” (Bills and 123)
  • For Name “Dolphins”, the first function returns the entire original Name “Dolphins”, since it is stand alone substring (no delimiter).  The second and third functions result in NULL strings
  • For Name “Patriots-1-34”, each function return a substring, since the original Name has two delimiters.  Patriots is first substring, 1 is second and 34 is third.  Each separated by delimiter “-“
  • For Name “Football–88”, the second function returns a blank string, since the second substring is blank and falls between consecutive delimiters (“–“)
  • For Name “-Steelers”, the first function returns a blank string, since the delimiter “-” appears at beginning of the original Name field

 

Feel free to comment if you have any further questions, or would like to see any additional examples.  Enjoy!

 

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

 

Leave a Comment