Skip to main content
waffle.svg
Domo Knowledge Base

ETL Actions: Edit Data

Version 15

 

Intro

This article describes in detail all of the Edit Data actions in ETL, including the following:

  • Filter Rows

  • Remove Duplicates

  • Replace Text

  • Set Column Value

  • String Operations

  • Text Formatting

  • Value Mapper

For information about creating an ETL DataFlow, see Creating an ETL DataFlow.

For information about the Data Center, see Data Center Layout.

Filter Rows

The Filter Rows action lets you include or exclude rows based on specified rules.

Example

For example, the Filter Rows action transforms this data...

... to this...

... using this configuration:

To configure the Filter Rows action,

  1. Click the Filter Rows action in the canvas.

  2. (Optional) Rename the action by clicking , then entering the name you want.

  3. Select whether to include rows that meet all or any of the rules you define.

  4. For each filter rule, do the following:

    1. Select a column to filter on.

    2. Select the operation to use.
       Operation items appear in the list, depending on the type of data in the column to filter on.

    3. Select whether to compare against values in a specific column or a specific value, then do one of the following:

      • (Conditional) If comparing against a column, select the column to use.

      • (Conditional) If comparing against a specific value, enter the value to use.

    4. (Conditional) If you want to add another rule, click Add Filter Rule.

Tip: You can preview the data transformed by an action by running a preview, clicking the action in the canvas, then clicking the Preview tab.

Remove Duplicates

The Remove Duplicates action lets you remove duplicate rows, based on specific columns.

Video - Using the Remove Duplicates Action

 


Example

For example, the Remove Duplicates action transforms this data...

... to this...

... using this configuration:

Note: Duplicate rows with the same base margin were removed.

To configure the Remove Duplicates action,

  1. Ensure that the column with values you want exists in the DataSet.

  2. Click the Remove Duplicates action in the canvas.

  3. (Optional) Rename the action by clicking , then entering the name you want.

  4. For each column with duplicate values you want to remove, do the following:

    1. Select the column you want.
      For example, "Product Base Margin". 

    2. Mark whether the comparison is case sensitive.

    3. (Conditional) If you want to add another column, then click Add Column Comparison.

Note: For a row to be removed, all of the columns selected in the Remove Duplicates tile must be duplicates.

 

Tip: You can preview the data transformed by an action by running a preview, clicking the action in the canvas, then click the Preview tab.

Replace Text

The Replace Text action lets you replace all occurrences of a text value with another text value (aka search and replace). You can use Java regular expressions. For more information about Java regular expressions, see https://docs.oracle.com/javase/tutorial/essential/regex/.

Tip: You could use the Set Column Type action to set a date or number column as text before (and after) using the Replace Text action to replace values.

For information about replacing text values using other actions, see Set Column Value and Value Mapper.

Example

For example, the Replace Text action replaces occurrences of the text value "Jumbo Box" in the "Product Container" column...

... with the text value "Ginormous"...

... using this configuration:

To configure the Replace Text action,

  1. Click the Replace Text action in the canvas.

  2. (Optional) Rename the action by clicking , then entering the name you want.

  3. Select the column to search in.

  4. Enter the text value you want to find.
    For example, "Jumbo Box". 

  5. (Option) Specify the find settings to use by clicking  in the field.

    Option

    Description

    Whole words

    Searches for whole words.

    Case sensitive

    Searches with case-sensitive values.

    Use RegEx

    Searches and replaces using Java regular expressions.

  6. Enter the text value you want to replace with.
    For example, "Ginormous". 

Tip: You can preview the data transformed by an action by running a preview, clicking the action in the canvas, then clicking the Preview tab.

Set Column Value

The Set Column Value action lets you replace the value of a column with the values in another column.

Notes: 

  • You can only replace values in one column with values from another column that have the same data type. For example, you can copy from a column with string values to another column with string values.
  • Both columns must already exist in the DataSet.

For information about replacing text values using other actions, see Replace Text and Value Mapper.

Example

For example, the Set Column Value action transforms this data...

... to this...

... using this configuration:

To configure the Set Column Value action,

  1. Ensure that the column with values you want exists in the DataSet.

  2. Click the Set Column Value action in the canvas.

  3. (Optional) Rename the action by clicking , then entering the name you want.

  4. For each column with values you want to replace, do the following:

    1. Select the column with values you want to replace.
      For example, "Product Base Margin". 

    2. Select the column with the values you want.
      For example, "Net Margin". 

    3. (Conditional) If you want to add a column, then click Add Column.

Tip: You can preview the data transformed by an action by running a preview, clicking the action in the canvas, then clicking the Preview tab.

String Operations

The String Operations action lets you substring, trim, or pad a string with spaces.

Example

For example, the String Operations action can transform this data...

... to this...

... using this configuration:

To configure the String Operations action,

  1. Click the String Operations action in the canvas.

  2. (Optional) Rename the action by clicking , then entering the name you want.

  3. Type the name of your new column.

  4. Then, choose the type of operation you want to perform.

  5. Select the column you want to perform this action on.

  6. Lastly, specify the beginning and ending character position you want from your string.

  7. (Optional) If you want to add another column, click Add String Operation.

Tip: You can preview the data transformed by an action by running a preview, clicking the action in the canvas, then clicking the Preview tab.

Text Formatting

The Text Formatting action lets you format text (lower case, upper case, capitalization), remove numbers, or remove everything except numbers.

Example

For example, the Text Formatting action transforms this data...

... to this...

... using this configuration:

To configure the Text Formatting action,

  1. Click the Text Formatting action in the canvas.

  2. (Optional) Rename the action by clicking , then entering the name you want.

  3. For each column you want to format, do the following:

    1. Select the text column you want.

    2. Select the type of letter-case format you want.

    3. Select whether to remove numbers or remove everything except numbers.

  4. (Conditional) If you want to add another column, click Add Column.

Tip: You can preview the data transformed by an action by running a preview, clicking the action in the canvas, then clicking the Preview tab.

Value Mapper

The Value Mapper action lets you search and replace string values in a specific column, according to pairs of string values you enter. (You might use the Value Mapper action for replacing abbreviations or converting language codes.) You can either have replacement values overwrite an existing column or be in a new column.

For information about replacing text values using other actions, see Replace Text and Set Column Value.

Example

For example, the Value Mapper action transforms this data...

... to this...

... using this configuration:

Note: This example shows values written to a new column. If configured, the values could overwrite values in the original column instead.

To configure the Value Mapper action,

  1. Click the Value Mapper action in the canvas.

  2. (Optional) Rename the action by clicking , then entering the name you want.

  3. Select the column you want to search.

  4. Select whether the values overwrite the values in the specified column or appear in a new column.

    1. (Conditional) If writing values to a new column, enter the name of the column.

  5. Select whether to write the original value or a default value when a match is not found in a row.

    1. (Conditional) If writing a default value, enter the value.

  6. For each value mapping you want, enter the value to search for and the value to replace with.

  7. (Conditional) If you want to add a mapping, then click Add Mapping.

Tip: You can preview the data transformed by an action by running a preview, clicking the action in the canvas, then clicking the Preview tab.