Transforming Data Using Replace

Transform data in a column using a simple pattern match or a regular (regex) expression. For example, in a State column, you might change CA to California.

You can use any Perl Compatible Regular Expression (PCRE), which are also referred to as regex expressions. Transforms are applied to every applicable row in a data set.

On the Home page, open a dataset or workbook.
- Select the dataset, click the Actions Menu , and then select Open. In the Data Diagram or Join Diagram, right-click a data source and click Open to display the transform editor.
- Select the workbook that contains the dataset, click the Actions Menu , select Open, then click Data. In the Data Diagram or Join Diagram, right-click a data source and click Open to display the transform editor.
  Note:CIC Analytics displays a Data Diagram for single-table datasets or a Join Diagram for multi-table datasets. For file-based data sources, Oracle Analytics displays the transform editor directly.
Select the text column that you want to transform.
Click Options, and then click Replace.
In the Replace step editor, specify how you'd like to match values.
Click Use regular expression to match using a complex regular expression (regex).

Click Match entire values only to change "male" to "M" but not "female" to "feM".
In the String to replace field and New string field, specify the literal text or regular expressions to search and replace (case-sensitive).
Examples:
- To change "2553 Bolman Court" to "2553 #Bolman# #Court#", select Use regular expression, enter ([A-Za-z]+) in the String to replace field, and enter #$1# in the New String field.
- To change "male" with "M", select Match entire values only, enter male in the String to replace field, and enter M in the New String field. . This changes "male" to "M" but will not change "female" to "feM".
When you complete both fields, you'll see a preview of the transformed data in the preview pane. Adjust the replace options until you get the transformation you want in the preview pane.
Click Add Step.
CIC Analytics adds a step to the Preparation Script panel.
To save your data preparation changes and apply them to your data, click Save Dataset.
If you're working with a single table dataset or a file-based dataset, click Apply Script.

Related Topics