| Excerpt |
|---|
Replaces values in the specified column or columns with the specified value, which can be a literal or an expression. Expressions can use conditional functions to filter the set of rows. |
The set transform is used to replace entire cell values. For replacement of partial cell values using literals or patterns, use the replace transform. See Replace Transform.
Literal example:
| D code |
|---|
| set col: Country value: 'USA' |
Output: Sets the values of all rows in the Country column to USA.
Multi-column Literal example:
| D code |
|---|
| set col: SSN,Phone value: '##REDACTED###' |
Output: Sets the values of all rows in the SSN and Phone columns to ##REDACTED##.
Expression example:
| D code |
|---|
set col: isAmerica value: IF(Country == 'USA', true', 'false') |
Output: If the value in the Country column is USA, then the value in isAmerica is set to true.
Placeholder example:
You can substitute a placeholder value for the column name, which is useful if you are applying the same function across multiple columns. For example:
| D code |
|---|
set col:score1,score2 value:IF ($col == 0, AVERAGE($col), $col) |
Output: In the above transform, the values in score1 and score2 are set to the average of the column value when the value in the column is 0. Note that the computation of average is applied across all rows in the column, instead of just the filtered rows.
Window function example:
You can use window functions in your set transforms:
| D code |
|---|
set col: avgSales value: ROLLINGAVERAGE(POS_Sales, 7, 0) group: saleDate order: saleDate |
Output: Calculate the value in the column of avgSales to be the rolling average of the POS_Sales values for the preceding seven days, grouped and ordered by the saleDate column. For more information, see Window Functions.
| D code |
|---|
set col:col1,[col2] value:(expression) [group: group_col] |
| Token | Required? | Data Type | Description |
|---|
| set | Y | transform | Name of the transform |
| col1 | Y | string | Column name |
| col2 | N | string | Column name |
| value | Y | string | Expression that generates the value to store in the column |
| group | N | string | If you are using aggregate or window functions, you can specify a group expression to identify the subset of records to apply the value expression. |
Identifies the column and optional additional columns to which to apply the transform.
| D code |
|---|
set col: MyCol value: 'myNewString' |
Output: Sets value in MyCol column to myNewString.
| Required? | Data Type |
|---|
| Yes | String (column name) |
| Include Page |
|---|
| value Parameter |
|---|
| value Parameter |
|---|
|
| Required? | Data Type |
|---|
| Yes | String (literal, column name, or expression) |
| Include Page |
|---|
| group Parameter |
|---|
| group Parameter |
|---|
|
If the value parameter contains aggregate or window functions, you can apply the group parameter to specify subsets of records across which the value computation is applied.
You can specify one or more columns by which to group using comma-separated column references.
| Required? | Data Type |
|---|
| No | String (column name) |
| Include Page |
|---|
| EXAMPLE - Replacement Transforms |
|---|
| EXAMPLE - Replacement Transforms |
|---|
|
| Include Page |
|---|
| EXAMPLE - Conditional Calculations Functions |
|---|
| EXAMPLE - Conditional Calculations Functions |
|---|
|
| D s also |
|---|
| label | wrangle_transform_set |
|---|
|
