Replace - script and chart function

Overview

A dataset of customer names contains some misspellings. This example uses the Replace function to process and replace any incorrect occurrence of 'Jhon' with 'John'.

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:

  • CustomerID

  • CustomerName

Load script

Example:
Load * inline [
CustomerID, CustomerName
1, Jhon Smith
2, Jhon Doe
3, John Williams
4, Jhonathan Harris
];

Results

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

• CustomerID

• CustomerName

Create the following calculated dimension:

• =Replace(CustomerName,'Jhon','John'), to replace all instances of Jhon with John.

The output of the Repeat function finds and corrects all instances of Jhon with John.

Overview

A dataset of products uses inconsistent labeling for the category codes. For example, electrical products have multiple code variations, such as ELEC, ELC, and elc. To standardize the codes, the Replace function is used to correct all inconsistent category codes for both electrical and furniture products.

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

  • CategoryCode

Load script

Example:
Load * inline [
ProductID, CategoryCode
1, ELEC
2, ELC
3, FURN
4, FRN
5, ELEC
6, ELC
7, elc
];

Results

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

• ProductID

• CategoryCode

Create the following calculated dimension:

• =Replace(Replace(Upper(CategoryCode), 'ELC', 'ELEC'),'FRN', 'FURN'), to replace any instances of ELC with ELEC and any instance of FRN with FURN.

The output of the Replace function has replaced any instances of ELC with ELEC, and FRN with FURN. Note that the code elc was also replaced. Although the Replace function is case-sensitive, the dimension formula uses the Upper function to conform the case of all CategoryCodes before replacing the string. This effectively renders the string replacement case-insensitive.