Replacing a Value

If you find that a value in your data table is misspelled, or, if one value has been entered on a different form than the other values in the column, you can replace that value directly from a table visualization, from the Details-on-Demand, or from the column overview in the expanded Data in analysis flyout, provided that the data is imported (not in-db).

Note: These steps are used to replace a few values in a column only, not to change the formatting of a column or to replace an entire data table. If you want to combine several different categories into one, you might want to use Group from Marked Categories instead.

To replace all occurrences of a value in a column:

1. In a table visualization, in the Details-on-Demand, or in the expanded data in analysis flyout, double-click on the value you want to replace.

   Comment: You can also right-click and select Replace Value from the pop-up menu.

2. Type a new value in the popover.
   

3. Make sure that All occurrences in column is selected.

4. Click Apply.

   Response: All instances of the value are replaced in the column and any visualizations using the data are updated. The replacement is added as a transformation on top of your data. The original data in your data source is not affected by this change. This means that if the underlying data is reloaded, the transformation will be reapplied after the reload and any additional rows matching the rule will also be replaced in the analysis. If the underlying value is changed in the source data, so that the transformation is no longer applicable, you will see an indication about this in the data canvas.

Tip: You can remove a previously added 'replace value'-operation from your data table. See Removing Operations in the Data Canvas for more information.

To replace a specific value in a column:

If you want to replace just a single occurrence of a value in a column, you must be able to identify the row, even if the data is reloaded. This is done by specifying one or more key columns for the data table, in the Data Table Properties. However, you do not have to specify the key columns before you realize that you want to replace a specific value; you can do it when it is needed.

1. In a table visualization, in the Details-on-Demand, or in the expanded data in analysis flyout, double-click on the value you want to replace.

   Comment: You can also right-click and select Replace Value from the pop-up menu.

2. Type a new value in the popover.

3. Make sure that This occurrence only is selected.

   Comment: If you cannot select this option, click on the Select key columns-link and specify one or more columns that uniquely identify the rows in the data table, then click OK. You might need to redo steps 1-3.

4. Click Apply.

   Response: The value is replaced and any visualizations using the data are updated. The replaced value is added as a transformation on top of your data. The original data in your data source is not affected by this change. This means that if the underlying data is reloaded, the transformation will be reapplied after the reload. If a previous key column identification is broken by the reload, so that more than one value is affected by the transformation, you will be informed about this in the data canvas. Also, if the underlying value is changed in the source data, so that the transformation is no longer applicable, you will see an indication about this in the data canvas.

Tip: You can use one of the procedures under Exporting Data to File or Exporting Data to Library to save the cleansed data table for reuse in other analyses.

Details

The underlying mechanism behind the Replace Value shortcut is a transformation. If you want to include some type of replaced value in a script, or when using the API to create custom tools, you can add similar transformations by using an expression.

For example, if [PK] is a column which uniquely identifies rows (that is, a primary key column) and you want to replace the specific value 'a' with 'b' on the row where [PK] is 34, you can write the following expression:

To replace all occurrences of  'a' with 'b' in [Col], write: