SubField - script and chart function

Overview

Open the Data load editor and add the load script below to a new tab.

The load script contains:

• A dataset which is loaded into a data table called Example.

• One field in the data table called Name.

Load script

Example:
Load * inline [
Name
Dave Owen
Joe Tem
];

Results

Load the data and open a sheet. Create a new table and add this field as a dimension:

• Name

Create the following calculated dimensions:

• =SubField(Name, ' ',1), to extract the first substring that appears before the space ' ' delimiter.

• =SubField(Name, ' ',-1), to extract the first substring that appears before the space ' ' delimiter, starting from the right of the string.

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

Load script

Open the Data load editor and add the following load script to a new tab.

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;

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• Name

• FirstName

• SurName

Explanation

The SubField function extracts the first substring, 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 substring. 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.

Overview

A dataset of products contains a product table. Each product in the table has a tag field that identifies the product area. Products can have multiple values for the tag field. For example, Widget A has the following tags: Electronics, Gadgets, Home. Tag values are separated by the pipe (|) character as delimiter. This example shows you how to extract specific tag values using the SubField function.

Open the Data load editor and add the load script below to a new tab.

The load script contains:

• A dataset which is loaded into a data table called Example.

• The following fields in the data table:

  • ProductID

  • ProductName

  • Tags

  • Sales

Load script

Example:
Load  *
INLINE [
ProductID, ProductName, Tags, Sales
1, Widget A, Electronics|Gadgets|Home, 150
2, Widget B, Electronics|Accessories, 200
3, Widget C, Furniture|Home, 100
4, Widget D, Gadgets|Accessories, 250
];

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• ProductID

• ProductName

• Tags

Create the following calculated dimensions:

• =SubField(Tags, '|',1), to extract the first item in the Tags field.

• =SubField(Tags, '|',2), to extract the second item in the Tags field.

• =SubField(Tags, '|',3), to extract the third item in the Tags field.

The output of the SubField functions has successfully returned the tags from the relevant positions in the original Tags string.

Load script

Open the Data load editor and add the following load script to a new tab.

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

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• Instrument

• Player

• Project

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.