The Transform Builder enables you to rapidly assemble complete transform steps through a simple menu-driven interface. After you select the transformation to apply, all relevant parameters can be configured through selection or type-ahead fields, so that you can choose from only the elements that are appropriate for the selected transformation.
To open the Transform Builder, begin creating a step through one of the following methods:
- Select a transformation from the Transformer toolbar. See Transformer Toolbar.
- Select a transformation from a column menu. See Column Menus.
- Search for and select a transformation in the Search panel. See Search Panel.
- Click New Step in the Recipe panel. See Recipe Panel.
- Edit an existing step.
Figure: Transform Builder
|Accept the currently selected item and move to the next field.|
|Move to the previous field.|
Step 1 - Select transformation in the Search Panel
From the Search panel, begin typing to see the list of available transformations. Select your preferred one.
union transforms have dedicated pages for configuring this transformations. You can enter
union as the transformation to open the corresponding tool.
Step 2 - Specify the column(s), formula, or condition
Depending on the transform that you have selected, you must specify one or more of the following types of parameters in the Transform Builder.
- Some transforms support combinations of the following.
- Some transforms, like
deduplicate, require no parameters.
The following are general categories of parameters:
- Literal values. A literal, or constant, value is a fixed numeric, string, Boolean, or other type of value, which does not change depending on the row under evaluation.
- Functions.Cloud Dataprep by TRIFACTA® supports a wide variety of numerical, statistical, and other function types.
- Columns. When a column name is used in a formula, the transform uses the value in the named column for the currently evaluated row.
- Operators. You can apply logical, numeric, or comparison operators as part of your formula.
Select or specify the column or columns to which to apply the transform.
Tip: To specify a range of columns, insert a tilde (
~) after the first column. The second column you select defines the last column in the range. Some transforms do not support multiple columns or column ranges.
For some transforms, you can specify patterns to identify conditions or elements of the data on which to take action. These matching patterns can be specified using one of the following types.
Tip: After you have used a pattern or string literal in one transform step, you can apply it in another. In the pattern field in the Transform Builder, click Browse Pattern History.
|Literal value||An exact string or value.|
The following matches on the exact value between the quotes:
Cloud Dataprep pattern
Cloud Dataprep by TRIFACTA supports a variety of macro-like pattern identifiers, which can be used in place of more complex regular expressions.
The following matches when two digits appear at the beginning of a value:
|Regular expression pattern|
Regular expressions are a standard method of describing matching patterns.
NOTE: The syntax of regular expressions can be complex and can lead to unexpected results if they are improperly specified. Regex is considered a developer-level skill.
The following matches on all numerical values from 0 to 99:
For more information on pattern-based matching, see Text Matching.
In the Transform Builder, transforms that require delimiter are organized into delimiter groups, so that you specify only the elements of a pattern that work together. Delimiter groups apply to the following transforms:
Delimiter groups are listed below.
|On delimiter||Transformation is applied based on a specific literal or pattern.|
|Between delimiters||Transformation is applied on database between two literal or pattern-based delimiters. Details are below.|
|On multiple delimiters|
Transformation is applied based on a sequence of delimiters. An individual pattern can be a string literal, Cloud Dataprep pattern, or regular expression, and the sequence can contain combinations of these pattern types.
|Between positions||Transformation is applied based on a starting index position and an ending index position. Index positions start from 0 on the left side of any cell value.|
|On positions||Transformation is applied based on a sequence of listed index positions. Index positions start from 0 on the left side of any cell value.|
|At regular interval||Transformation is applied at every nth position. Index positions start from 0 on the left side of any cell value.|
For more information on the underlying syntax for delimiter groups, see Pattern Clause Position Matching.
Between two delimiters
Matches any values that appear between two delimiters. One delimiter describes the beginning of the match, and the other delimiter describes the end of the match.
Each delimiter can either include or exclude the matching value:
|Transform Builder option||Include as part of transform||Include/Exclude|
|Start delimiter||false||Excludes sub-pattern|
|Start delimiter||true||Includes sub-pattern|
|End delimiter||false||Excludes sub-pattern|
|End delimiter||true||Includes sub-pattern|
A condition is an expression that yields a
false value. A condition may include all of the elements of a formula. This value determines whether the transformation is applied to the evaluated row.
Step 3 - Grouping, Ordering, and Naming
A number of transforms support the following parameters.Group parameter: For transforms that aggregate data, such as
window, you can specify the column by which you wish to group the computed aggregations. In the following example, all values in the
Sales column are summed up for each value in the
pivot value:SUM(Sales) group:State
Assuming that there are entries in the
State column for each state in the United States, the resulting transform step has 50 rows, each of which contains the total sales for the listed state.
Order parameter: Some transforms support the
order parameter, which allows you to specify the column of values that are used to sort the output. In the following example, all aggregates
Sales values are ordered by the contract date and grouped by State:
pivot value:SUM(Sales) group:State order:contractDate
New Column Name parameter: For transforms that generate new columns, such as
extract, you can optionally specify the name of the new column, which saves adding a step to rename it. In the following example, the values of
colB are summed and written to the new column
derive type:single value:(colA + colB) as:'colC'
Step 3 - Specify other parameters
Depending on the transform, you may be presented with other required or optional parameters to specify. See Transforms.
Step 4 - Add the step
When you have finished your transform step, review the preview in the data grid.
If the results look ok, click Add.
The step is added to your recipe and applied to the data grid.
Edit a transform
After you have added a step, you can modify it as needed. In the Recipe panel, select the Pencil icon next to the recipe step. The step is displayed for editing in the Transform Builder.