# SubField - script and chart function

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 ])

Return data type: string

Arguments:

Arguments

| Argument | Description |
| --- | --- |
| text | The original string. This can be a hard-coded text, a variable, a dollar-sign expansion, or another expression. |
| delimiter | A character within the input text that divides the string into component parts. |
| field\_no | The optional third argument is an integer that specifies which of the substrings of the parent string text is to be returned. Use the value 1 to return the first substring, 2 to return the second substring, and so on.<br><br><ul> <li>If <span>field_no</span> is a positive value, substrings are extracted from left to right.</li> <li>If <span>field_no</span> is a negative value, substrings are extracted from right to left.</li> </ul> |

Tip note SubField() can be used instead of using complex combinations of functions such as Len(), Right(), Left(), Mid(), and other string functions.

Examples: Chart expressions

| Example | Result |
| --- | --- |
| `SubField(S,<br>';' ,2)` | Returns 'cde'<br>if S is 'abc;cde;efg'. |
| `SubField(S,<br>';' ,1)` | Returns an empty string if S is an empty string. |
| `SubField(S,<br>';' ,1)` | Returns an empty string if S is ';'. |
| Suppose you have a variable that holds a path name vMyPath,<br><br>`Set vMyPath=\Users\ext_jrb\Documents\Qlik\Sense\Apps;`. | In a text & image chart, you can add a measure such as:<br>`SubField(vMyPath, '\',-3``)`, which results in 'Qlik', because it is the substring third from the right-hand end of the variable vMyPath. |

## Examples: Script and chart expressions using SubField

[](https://help.qlik.com/en-US/sense/Subsystems/Hub/Content/Sense_Hub/Scripting/StringFunctions/SubField.htm)

#### Examples - script and chart expressions

### Basic examples

| Example | Result |
| --- | --- |
| `SubField(S,<br>';' ,2)` | Returns 'cde'<br>if S is 'abc;cde;efg'. |
| `SubField(S,<br>';' ,1)` | Returns an empty string if S is an empty string. |
| `SubField(S,<br>';' ,1)` | Returns an empty string if S is ';'. |
| Suppose you have a variable that holds a path name vMyPath,<br><br>`Set vMyPath=\Users\ext_jrb\Documents\Qlik\Sense\Apps;`. | In a text & image chart, you can add a measure such as:<br>`SubField(vMyPath, '\',-3``)`, which results in 'Qlik', because it is the substring third from the right-hand end of the variable vMyPath. |

### Script example 1

#### Load script

Load the following script expressions and data in the data load editor.

```
FullName:
					
LOAD * inline [
Name
'Dave Owen'
'Joe Tem'
];

SepNames:

Load Name,
SubField(Name, ' ',1) as FirstName,
SubField(Name, ' ',-1) as Surname
Resident FullName;
Drop Table FullName;
```

#### Create a visualization

Create a table visualization in a Qlik Sense sheet with Name, FirstName, and SurName as dimensions.

#### Result

| Name | FirstName | SurName |
| --- | --- | --- |
| Dave Owen | Dave | Owen |
| Joe Tem | Joe | Tem |

#### Explanation

The SubField() function extracts the first substring of Name by setting the field\_no argument to 1. Since the value of field\_no is positive, a left to right order is followed for extracting the subtring. A second function call extracts the second substring by setting the field\_no argument to -1, which extracts the substring following a right to left order.

### Script example 2

#### Load script

Load the following script expressions and data in the data load editor.

```
LOAD DISTINCT
Instrument,
SubField(Player,',') as Player,
SubField(Project,',') as Project;

Load * inline [
Instrument|Player|Project
Guitar|Neil,Mike|Music,Video
Guitar|Neil|Music,OST
Synth|Neil,Jen|Music,Video,OST
Synth|Jo|Music
Guitar|Neil,Mike|Music,OST
] (delimiter is '|');
```

#### Create a visualization

Create a table visualization in a Qlik Sense sheet with Instrument, Player, and Project as dimensions.

#### Result

| Instrument | Player | Project |
| --- | --- | --- |
| Guitar | Mike | Music |
| Guitar | Mike | Video |
| Guitar | Mike | OST |
| Guitar | Neil | Music |
| Guitar | Neil | Video |
| Guitar | Neil | OST |
| Synth | Jen | Music |
| Synth | Jen | Video |
| Synth | Jen | OST |
| Synth | Jo | Music |
| Synth | Neil | Music |
| Synth | Neil | Video |
| Synth | Neil | OST |

#### Explanation

This example shows how using multiple instances of the Subfield() function, each with the field\_no parameter left out, from within the same LOAD statement creates Cartesian products of all combinations. The DISTINCT option is used to avoid creating duplicate records.