You are here: Tools > All Tools > Dynamic Replace

Dynamic Replace Tool

The Dynamic Replace tool allows the user to quickly replace data values on a series of fields, based on a condition. The data replacement can be based on formulas or static values.

Say you have a hundred different income fields and instead of the actual value in each field, you want to represent the number with a code of A, B, C, D, etc. that represents a range. The Dynamic Replace tool can easily perform this task.

The Dynamic Replace tool accepts 2 inputs - Data and Replacement Data. The left input is the original data needing replacement and is indicated by a D. The right input is for the Replacement Data and is indicated by an R. The Replacement data stream must contain the following 3 fields: Field Name, Output Value, and an Expression field. The tool will match the Field Name field of the R connection to the field names in the D connection, and replace the value of the D connection with the Output Value of the R connection, using the Expression field.

The Dynamic Replace tool will stop at the first match, so you do not have to test for both sides of a range.

Configuration Properties

  1. D Input (original data) requires no configuration.
  2. R Input (Replacement Data). Configure the following fields:
    1. Field Name Field: These fields will have their data replaced. Select the field from the Replacement Data R input that contains the field names in the original data D input.
    2. Boolean Expression Field: The Boolean Expression field is a condition where any value other than zero gets run through the tool. Select the field from the Replacement Data R input that contains the expression to use to determine the replacement.
    3. Output Value Field: This is the data replacement. This field can contain static values or be formulas that execute on data being sent through the tool. Select the field from the Replacement Data R input that contains the replacement values.
    4. Field type Handling: Since this tool swaps values in a field, the field type may need to be changed to accommodate the new data values. There are three options for changing the field type:
      1. Autofield Field Types: The replacement data is examined and the field type is automatically set to accommodate the data contained within the column.
      2. Keep Original Field Types: The original field type is maintained. If the field type is incompatible for the replacement data being passed through, an error or conversion error will result.
      3. Change Field Types to: The user can specify the field type and size. If the field type is incompatible for the replacement data being passed through, an error conversion error will result.
        • Type: Use the drop down to pick the desired field type.
        • Size: Type the numeric value for the field size.
        • Some field types, such as Spatial Object, do not require a field size. In this case, the size parameter is ignored.

    5. Warn/Error on Unmatched Data Fields: Select the intended behavior for unmatched data. Choices include:

Click Apply to have the configurations accepted.

Tool Output

The Dynamic replace tool has two outputs available for downstream processing. Because of the dynamic nature of this tool, the Output Properties Panel will display the input properties before runtime and after runtime it will display the output properties from the last run.

  1. O Output: This is the original data structure with the replacement data. The field types will have been changed relative to the data replacement.
  2. S Output: This is a Summary of the replacements. Included in this output are counts of each Field Name and Output Value combinations that were replaced.

Example Use Case

Replace Income values across multiple fields with a code that represents a range of values, like these:

Range Values

Output Value

End of Range

A

20,000

B

40,000

C

60,000

D

80,000

E

100,000

The Dynamic Replace tool will stop at the first match, so you do not have to test for both sides of a range, but you do have to make sure your Expressions are sorted properly (in ascending order).

Original Data Input

Income_2000

Income_2005

Income_2010

Income_2015

35,234

36,954

74,852

41,555

47,895

85,214

42,657

91,247

62,154

74,123

95,236

72,453

23,147

54,888

12,458

68,321

Replacement Data Input

The tool will match the Field Name field of the R connection to the field names in the D connection, and replace the value of the D connection with the Output Value of the R connection, using the Expression field.

Name

OutputValue

Boolean Expression

Income_2000

A

_CurrentField_<ToNumber(ToString(20000))

Income_2005

A

_CurrentField_<ToNumber(ToString(20000))

Income_2010

A

_CurrentField_<ToNumber(ToString(20000))

Income_2015

A

_CurrentField_<ToNumber(ToString(20000))

Income_2000

B

_CurrentField_<ToNumber(ToString(40000))

Income_2005

B

_CurrentField_<ToNumber(ToString(40000))

Income_2010

B

_CurrentField_<ToNumber(ToString(40000))

Income_2015

B

_CurrentField_<ToNumber(ToString(40000))

Income_2000

C

_CurrentField_<ToNumber(ToString(60000))

Income_2005

C

_CurrentField_<ToNumber(ToString(60000))

Income_2010

C

_CurrentField_<ToNumber(ToString(60000))

Income_2015

C

_CurrentField_<ToNumber(ToString(60000))

Income_2000

D

_CurrentField_<ToNumber(ToString(80000))

Income_2005

D

_CurrentField_<ToNumber(ToString(80000))

Income_2010

D

_CurrentField_<ToNumber(ToString(80000))

Income_2015

D

_CurrentField_<ToNumber(ToString(80000))

Income_2000

E

_CurrentField_<ToNumber(ToString(100000))

Income_2005

E

_CurrentField_<ToNumber(ToString(100000))

Income_2010

E

_CurrentField_<ToNumber(ToString(100000))

Income_2015

E

_CurrentField_<ToNumber(ToString(100000))

" _CurrentField_" refers to the Field Name Field that is currently being processed. It is used so that the expression does not have to be constructed for each Name.

Output of Dynamic Replace

This is the original data structure with the replacement data. The field types will have been changed relative to the data replacement.

Income_2000

Income_2005

Income_2010

Income_2015

B

B

D

C

C

E

C

E

D

D

E

D

B

C

A

D

 

Summary Output of Dynamic Replace

This is a Summary of the replacements. Included in this output are counts of each Field Name and Output Value combinations that were replaced. This data view has been truncated for demonstration purposes.

FieldName

OutputValue

Count

Income_2000

A

0

Income_2000

B

2

Income_2000

C

1

Income_2000

D

1

Income_2000

E

0

Income_2000

[Null]

0

Income_2005...

A...

0...