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

• The following fields in the data table:

  • InputText

  • CharsToPurge

Load script

Example:
Load * inline [
InputText, CharsToPurge
'a1b2c3', '123'
'a^b^c', '^'
'A^b^c', 'A^'
];

Results

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

• InputText

• CharsToPurge

Create the following calculated dimension:

• =PurgeChar(InputText,CharsToPurge), to calculate the characters in InputText that match the characters in CharsToPurge and return only those characters that do not match.

The output of the PurgeChar function returns only those characters from InputText that do not match the characters in CharsToPurge. For example, the first line returns bc and removes the other characters.

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

Example:
Load
*, 
purgechar(String1, String2) as PurgeChar;
Load * inline [
String1, String2
'a1b2c3', '123'
];

Overview

A dataset contains customer names and phone numbers. This example parses the phone number strings to retain only numerical characters and discard all other characters, such as parentheses, dashes, periods, plus signs, and spaces.

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:

  • CustomerName

  • PhoneNumber

Load script

Example:
Load * inline [
CustomerName, PhoneNumber
John Doe, (123) 456-7890
Jane Smith, 123-456-7890
Bob Johnson, 123.456.7890
Alice Brown, +1 (123) 456 7890
];

Results

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

• CustomerName

• PhoneNumber

Create the following calculated dimension:

• =PurgeChar(PhoneNumber, '()- .+ '), to extract the non-numeric characters from the PhoneNumber field and return only numeric characters.

The output of the PurgeChar function has successfully removed all non-numeric characters from the PhoneNumber string and returned only numbers.