There may be occasions where there is a field from one of your data sources that contains more than one value, and you need to split these values out into separate columns. This can be done by adding fields to your table, and then using field transformations to parse out the values from the source field and populate the added fields accordingly. This article uses the example of a source field that has two values in it separated by a space that are parsed out and saved out to two new fields that were added to the table.
Source field that contains two values separated by a space.
The requirement is to split out the two numbers in the field above and store them in two new separate fields, which can be done according to the following general steps:
- Adding two new fields to the table, one to store the left side value and another to store the right side value.
- Adding Field Transformations to the new fields to populate the as parsed out from the source field.
Add a Field for the Left Side Value and Adding a Field Transformation to Populate the field
Right-click on the Prepare Instance table and select Add Field to create the new field that will be used to hold the left side value from the source field.
For compatiblity reasons, it is usually best to create the new field using the same data type as the source field.
Right-click on the newly added field and select “Field Transformations”.
In the Field Transformations pane on the right, leave the Operator at “Custom value” and then click the Add button at the bottom.
Add the SQL Script that will that corresponds to the Field Transformation needed to parse out the left side value from the source field. The syntax for this Field Transformation will be similar to the following, where [Dual_Figure] is the source field and has been parameterized by dragging it over from the Data Fields available in the pane on the right.
LEFT([Dual_Figure], CHARINDEX(' ',[Dual_Figure]) - 1)
Click OK to add the Field Transformation, which will then be visible beneath the field.
Add a Field for the Right Side Value and use a Field Transformation to Populate the field
Right-click on the Prepare Instance table and select Add Field to create the new field that will be used to hold the right side value from the source field.
Right-click on the newly added field and select “Field Transformations”.
In the Field Transformations pane on the right, leave the Operator at “Custom value” and click the Add button at the bottom.
Add the SQL Script that will that corresponds to the Field Transformation needed to parse out the right side value from the source field. The syntax for this Field Transformation will be similar to the following, where [Dual_Figure] is the source field and has been parameterized by dragging it over from the Data Fields available in the pane on the right.
RIGHT([Dual_Figure], LEN([Dual_Figure]) - CHARINDEX(' ',[Dual_Figure]))
The two transformations are now visible beneath the two newly added fields.
Validate the two fields are populated correctly with the values from the source field.
Deploy and Execute the table to validate and populate the transformations.
Preview the table to verify that the data in the Dual_Figure column has been split into the AccountNo and InternalNo columns based on the space delimiter.
