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:
Load * inline
Subfield(Name,'-',1) as Name1,
Subfield(Name,'-',2) as Name2,
Subfield(Name,'-',3) as Name3
DROP Table TeamNames;
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:
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!