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: 

set col: Country value: 'USA'

Output: Sets the values of all rows in the Country column to USA

Multi-column Literal example: 

set col: SSN,Phone value: '##REDACTED###'

Output: Sets the values of all rows in the SSN and Phone columns to ##REDACTED##

Expression example:

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:

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:

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.

set col:col1,[col2] value:(expression) [group: group_col] 

TokenRequired?Data TypeDescription
setYtransformName of the transform
col1YstringColumn name
col2NstringColumn name
valueYstringExpression that generates the value to store in the column
groupNstringIf you are using aggregate or window functions, you can specify a group expression to identify the subset of records to apply the value expression.

col1, col2

Identifies the column and optional additional columns to which to apply the transform.

set col: MyCol value: 'myNewString'

Output: Sets value in  MyCol column to myNewString.

Required?Data Type
YesString (column name)

value

Required?Data Type
YesString (literal, column name, or expression)

group

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
NoString (column name)


Example - Clean up marketing contact data with replace, set, and extract

Example - Using $col placeholder