Trim - Script and chart function

Overview

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

The load script contains:

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

• One field in the data table called InputText.

Load script

Set verbatim=1;
Example:
Load * inline [
InputText
'   abc    '
' def  '];

Results

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

• InputText

Create the following calculated dimension:

• =Trim(InputText) to remove any extra leading or trailing spaces from InputText.

The output of the Trim function removes all leading and trailing spaces.

Overview

A customer relationship management (CRM) system contains records with inconsistent data entry that include extra leading spaces. For reporting purposes, the data requires cleaning to remove these spaces and to ensure proper sorting and grouping of customer names.

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

The load script contains:

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

• One field in the data table called CustomerName.

Load script

Set verbatim=1;
Example:
Load * inline [
CustomerName
'  John Doe '
'Jane Smith '
'   Michael Johnson'
'Emily Davis'
];

Results

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

• CustomerName

Create the following calculated dimension:

• =Trim(CustomerName) to remove any extra leading or trailing spaces from CustomerName.

The output shows that the Trim function removed all leading and trailing spaces from the original string values in CustomerName.

Overview

This example removes all leading and trailing spaces from the original text string. The chart expression includes measures that use the Len function to count the characters in the string before and after using the Trim function.

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

The load script contains:

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

• One field in the data table called String.

Load script

Set verbatim=1;
Example:
Load * inline [
String
'   abc    '
' def  '];

Results

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

• String

Create the following calculated dimension:

• =Trim(String), to remove any extra leading or trailing spaces

Create the following measures:

• =Len(String), to count the length of the original string

• =Len(Trim(String)), to count the length of the string after the spaces have been removed.

When you compare the output of the Trim function to the original string values in the script, you can see how all leading and trailing spaces have been removed.

The following code shows how to use the function in a load script.

Set verbatim=1;
Example:
Load *, len(TrimString) as TrimStringLength;
Load *, trim(String) as TrimString;
Load *, len(String) as StringLength;
Load * inline [
String
'   abc    '
' def  '](delimiter is '\t');