You are here:

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

D Input (original data) requires no configuration.
R Input (Replacement Data). Configure the following fields:

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.
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.
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.

Values are Expressions/Formulas: Check this box if the values in the specified field are formulas. The formula will be executed at the record level for each field specified in step 1, based on the condition in step 2.

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:

Autofield Field Types: The replacement data is examined and the field type is automatically set to accommodate the data contained within the column.
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.
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.

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

Allow unmatched data fields: Unmatched data fields will get replaced with [Null] values. No message will be reported in the Results window.
Warn on unmatched data fields: Unmatched data fields will get replaced with [Null] values. A Warning message will be reported for each unmatched field in the Results window.
Error on unmatched data fields: An error will be thrown upon the first encounter with an unmatched data field and processing will stop.

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.

O Output: This is the original data structure with the replacement data. The field types will have been changed relative to the data replacement.
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...