# 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:

SubField 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.<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 and results:

Examples 1-3

| Example | Result |
| --- | --- |
| ` SubField('abc;cde;efg', ';', 2)  ` | Returns 'cde' |
| ` SubField('', ';', 1)  ` | Returns NULL |
| ` SubField(';', ';', 1)  ` | Returns an empty string |

Add the example script to your document and run it. Then add, at least, the fields listed in the results column to a sheet in your document to see the result.

`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;`

Example 4

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

Suppose you have a variable that holds a path name vMyPath,

`Set vMyPath=\Users\ext_jrb\Documents\Qlik\Sense\Apps;`.

In a text & image chart, you can add a measure such as:  
`SubField(vMyPath, '\',-3``)`, which results in 'Qlik', because it is the substring third from the right-hand end of the variable vMyPath.

This example shows how multiple rows are created from a single instance of Subfield().

Add the example script to your document and run it. Then add, at least, the fields listed in the results column to a sheet in your document to see the result.

`LOAD DISTINCT` `Player,` `SubField(Project,',') as Project;` ` ` `Load * inline [` `Player|Project` `Neil|Music,OST` `Jo|Music` `Mike|Music,OST,Video` `] (delimiter is '|');`

Example 5

| Player | Project |
| --- | --- |
| Neil | Music |
| Neil | OST |
| Jo | Music |
| Mike | Music |
| Mike | OST |
| Mike | Video |

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 record.

Add the example script to your document and run it. Then add, at least, the fields listed in the results column to a sheet in your document to see the result.

`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 '|');`

Example 6

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