Text functions

1. Extract

Reports may often contain concatenated values that need to be split. Use the Extract option to extract a string or character from a text field or split a text field.

You can choose different options, such as range, length, first/last character, or splitting the string based on a delimiter.

1.1. Range

Extracts text based on the start and end position of the string to be extracted. Let's extract the city code from the "AreaCode" column. We need to specify the starting position of the text to be extracted and the number of characters to extract.

The city code is extracted from the area code and captured as a separate dimension.

1.2. Length

Capture the text length in a separate dimension by choosing the Length option.

The length of the "Category" column has been extracted into a new dimension titled "Category length".

1.3. First characters

Extract the first n characters from a text field. Let's pull the ID (first 5 characters) from the Customer ID field.

Extract configuration to pull the first 5 characters from a field.

A new dimension containing the ID is created.

1.4. Last characters

Similar to the first characters option, Last Characters will pull the last n characters from a string.

1.5. Text before delimiters

Extracts the text before a specific delimiter. Let's extract the currency code from the "Discount Index" field.

Select the Text Before Delimiters option and enter "-" in the Delimiter text box.

The currency code has been extracted into a separate dimension.

1.6. Text after delimiters

Extracts the text after a specific delimiter. Let's take the same example to extract the frequency from the Discount Index column. Select the Text After Delimiters option and enter "-" in the Delimiter text box.

You'll see that a new dimension is created containing the text after the specified delimiter.

2. Format

Use the format option to perform operations like changing the case, trimming or adding a prefix/suffix to your text data.

2.1. Changing the case

We may need to change the case of source data to ensure data consistency or search accuracy for case-sensitive systems. Let's change the case of the "Category" and "Segment" fields to uppercase.

Notice how the data in both fields is in uppercase.

2.2. Trim

Leading or trailing spaces can cause mismatches in comparisons and can lead to inconsistencies in your data. The Account field has trailing spaces, so we'll use the Trim option to remove them.

The trailing spaces in the Account field have now been trimmed:

2.3. Add prefix/suffix

Adding a prefix or suffix can help in easy identification and categorization of data. They are also used in generating unique identifiers. Let's add a prefix to the Account number.

You'll notice that the account numbers have an "ACC" prefix added.