Altair SmartWorks Analytics

 

Replacing Values

The Replace operation is found within the Column Changes node and allows you to replace strings with a new string of your choice. This operation is applicable to fields of all data types, but the specific replace functionalities vary according to the data type. Replace Nulls with Aggregation and Replace Nulls with Boolean, for example, are only applicable to numeric and boolean columns, respectively, while Ditto is applicable to columns of all data types.  

Prerequisites

  • An Execution Profile with an active session linked to the workflow

  • A Column Change node connected to a Data Frame node

  •  

Steps

  1. Configure the Column Changes node by double-clicking on the node or using the Open option provided in the node menu.

  2. The table displays in the Node Viewer.

  3. In the Configuration tab, select a column containing values you wish to replace and then click on the Replace tab in the Column Changes menu.

 Replace Using Position and Length

This functionality replaces a string identified by position and length with another string of your choice. Note that, compared with the Find and Replace functionality, the Replace Using Position and Length functionality is less specific because it does not identify strings using their actual characters. If you require a replacement operation to modify exact values, the Find and Replace option may be better suited for you.  

Steps

  1. Select the column containing a string you'd like to replace with another string and then click Replace > Using Position and Length.

  2. Specify a position (from the left of the value) to use as a starting point for string replacement in the Using Position field.

  3. Specify a length of string to replace in the Using Length field.

  4. Specify the string to use as a replacement for the string defined by Steps 2 and 3 in the Replace With field.  

  5. In the example below, we wish to change the year in column Ship Date from 2010 to 2020. Thus, the string "10" identified by position 9 and length 2 must be replaced with "20."

     

  6. If you wish to create a new column for your newly replaced rows, tick the Create New Column box and provide a name for the new column.

  7. If you wish to apply the replace operation to multiple columns, click on the Apply to multiple columns link and then choose which columns to apply the same operation to. Click Apply when you are done to save your selection(s). This option is disabled if you choose to create a new column in which to display the replaced rows.

  8. Select Apply when you are done.

  9. You can abandon the replace operation by clicking the Cancel button instead.

    If you chose to create a new column, the new column is added to your table. If you did not choose to create a new column, the field values in the original column are updated, as shown below.

     

  10. To check the code that will be executed for your specified Column Changes configuration, save your specifications and then click on the Code tab of the Column Changes Node Viewer. You can also use the tab that displays to refine the code further.

  11.  

  12. To complete the Column Changes node configuration, press Save. To cancel your changes and return to the Workflow Canvas, press Discard or simply close the Column Changes Node Viewer. To execute your column changes, click the Run button.

Find and Replace

This functionality searches for a specific string and then replaces it with another string of your choice.

Steps

  1. Select the column containing strings you'd like to replace with another string and then click Replace > Find and Replace.

  2.  

  3. Specify the string to replace in the Find field.

  4. Specify a string to use as a replacement for the string specified in Step 2 in the And Replace with field.  

  5. Tick the box for Ignore case if you would like the application to ignore case when looking for the specified characters to remove.

  6. If you wish to create a new column for your newly replaced rows, tick the Create New Column box and provide a name for the new column.

  7. If you wish to apply the replace operation to multiple columns, click on the Apply to multiple columns link and then choose which columns to apply the same operation to. Click Apply when you are done to save your selection(s). This option is disabled if you choose to create a new column in which to display the replaced rows.

  8. Select Apply when you are done.

  9. You can abandon the replace operation by clicking the Cancel button instead.

    If you chose to create a new column, the new column is added to your table. If you did not choose to create a new column, the field values in the original column are updated, as shown below.

     

  10. To check the code that will be executed for your specified Column Changes configuration, save your specifications and then click on the Code tab of the Column Changes Node Viewer. You can also use the tab that displays to refine the code further.

  11.  

  12. To complete the Column Changes node configuration, press Save. To cancel your changes and return to the Workflow Canvas, press Discard or simply close the Column Changes Node Viewer. To execute your column changes, click the Run button.

Replace Using Ditto

The Ditto function is used to replace blank and null rows with the value above it.  

Simply select Replace > Ditto to modify the values in your column.

In the example below, the left-hand shows an original column with null values. The right-hand column shows the same column with the Ditto function applied to it.

 

Replace Nulls with Specified Characters

This functionality replaces nulls with a specific string.

Steps

  1. Select the column containing nulls and then click Replace > Nulls with Specified Characters.

  2.  

  3. Specify the string to replace nulls with in the Nulls with Specified Characters field.

  4. If you wish to create a new column for your newly replaced rows, tick the Create New Column box and provide a name for the new column.

  5. If you wish to apply the replace operation to multiple columns, click on the Apply to multiple columns link and then choose which columns to apply the same operation to. Click Apply when you are done to save your selection(s). This option is disabled if you choose to create a new column in which to display the replaced rows.

  6. Select Apply when you are done.

  7. You can abandon the replace operation by clicking the Cancel button instead.

    If you chose to create a new column, the new column is added to your table. If you did not choose to create a new column, the field values in the original column are updated, as shown below.

     

  8. To check the code that will be executed for your specified Column Changes configuration, save your specifications and then click on the Code tab of the Column Changes Node Viewer. You can also use the tab that displays to refine the code further.

  9.  

  10. To complete the Column Changes node configuration, press Save. To cancel your changes and return to the Workflow Canvas, press Discard or simply close the Column Changes Node Viewer. To execute your column changes, click the Run button.

Replace Values with Nulls

This functionality replaces specific strings with null values.

Steps

  1. Select the column containing values you'd like to replace with nulls and then click Replace > Values with Nulls.

  2.  

  3. Specify the string to replace with nulls in the Value with Nulls field.

  4. If the column you selected has a data type of Float32/64, you can specify a number that will be used to create a range of values that should be replaced with nulls. For example, if you specify a number of 35.96 in the Value with Nulls field and then specify a number of 2 in the Tolerance field, all values of 35.96 ± 2 (i.e., 33.96–37.96) will be replaced with nulls. Similarly, if you specify a number of -60 in the Value with Nulls field and then indicate a number of 5 in the Tolerance field, all values in the range of (-65)–(-55) will be replaced with nulls. This field is not available for columns of other data types (e.g., Int, Text), and the Tolerance value cannot be a negative number.

  5. Tick the box for Ignore case if you would like the application to ignore case when looking for the specified characters to remove.

  6. If you wish to create a new column for your newly replaced rows, tick the Create New Column box and provide a name for the new column.

  7. If you wish to apply the replace operation to multiple columns, click on the Apply to multiple columns link and then choose which columns to apply the same operation to. Click Apply when you are done to save your selection(s). This option is disabled if you choose to create a new column in which to display the replaced rows.

  8. Select Apply when you are done.

  9. You can abandon the replace operation by clicking the Cancel button instead.

    If you chose to create a new column, the new column is added to your table. If you did not choose to create a new column, the field values in the original column are updated. In the example below, the value "35.96" with a tolerance of "2" is replaced with nulls.

     

  10. To check the code that will be executed for your specified Column Changes configuration, save your specifications and then click on the Code tab of the Column Changes Node Viewer. You can also use the tab that displays to refine the code further.

  11.  

  12. To complete the Column Changes node configuration, press Save. To cancel your changes and return to the Workflow Canvas, press Discard or simply close the Column Changes Node Viewer. To execute your column changes, click the Run button.

Replace Blank Values with Specified Characters

This functionality replaces blanks with a specific string.

Steps

  1. Select the column containing blanks and then click Replace > Blank Values with Specified Characters.

  2. Specify the string to replace blanks with in the Blank Values with Specified Characters field.

  3.  

  4. If you wish to create a new column for your newly replaced rows, tick the Create New Column box and provide a name for the new column.

  5. If you wish to apply the replace operation to multiple columns, click on the Apply to multiple columns link and then choose which columns to apply the same operation to. Click Apply when you are done to save your selection(s). This option is disabled if you choose to create a new column in which to display the replaced rows.

  6. Select Apply when you are done.

  7. You can abandon the replace operation by clicking the Cancel button instead.

    If you chose to create a new column, the new column is added to your table. If you did not choose to create a new column, the field values in the original column are updated. In the example below, the left-hand column shows the original column with blanks; in the right-hand column, all blanks are replaced with the value "Discontinued Model."

     

  8. To check the code that will be executed for your specified Column Changes configuration, save your specifications and then click on the Code tab of the Column Changes Node Viewer. You can also use the tab that displays to refine the code further.

  9.  

  10. To complete the Column Changes node configuration, press Save. To cancel your changes and return to the Workflow Canvas, press Discard or simply close the Column Changes Node Viewer. To execute your column changes, click the Run button.

Replace Nulls with Aggregation (Numeric Columns)

This functionality replaces null values in numeric columns with an aggregation, including mean, median, standard deviation, or variance.

Select the numeric column containing nulls that you wish to replace with an aggregation and then click Replace > Replace Nulls with Aggregation > <type of aggregation>.

Note that when the mean, median, standard deviation, and variance are applied to a column, these aggregations are calculated on the basis of the number of records displayed in the data preview of the Column Changes node. When the node configuration is saved and run, the actual calculations are performed on the basis of the entire dataset.

Replace Nulls with Boolean (Boolean Columns)

The Replace Nulls with Boolean functionality replaces all null values in a boolean column with values of true or false. Select the boolean column containing null values you wish to replace and then click Replace Nulls with Boolean > Replace Nulls with True/Replace Nulls with False.

Redacting Values in a Column

You may wish to mask the values of a specific column. SmartWorks Analytics' Redaction function allows you to do so with just a few clicks. Select the column containing values you'd like to mask and then click Replace > Redact. Choose the type of redaction to apply:

  • X over text - Replaces all text characters with X (e.g., 10 XXXXXX XXXXX)

  • X over digits - Replaces all numbers with X (e.g., XX little ducks)

  • X over non-blank - Replaces all non-blanks with X (e.g., XX XXXXXX XXXXX)

  • X except last four digits - Replaces all numbers except last four digits (e.g., XXXX-XXXX-XXXX-1234)