Page tree

 

Support | BlogContact Us | 844.332.2821

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migration of unmigrated content due to installation of a new plugin

D toc

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.

D s
snippetBasic

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 s
snippetParameters
D code

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

ParameterRequired?Tranform BuilderData TypeDescription
col1YColumnstringColumn name
col2NColumnstringColumn name
valueYFormulastringExpression that generates the value to store in the column
groupNGroup bystringIf you are using aggregate or window functions, you can specify a group expression to identify the subset of records to apply the value expression.

d-s-lang-notes

col1, col2

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.

D s
snippetusage

Required?Data Type
YesString (column name)

value

Include Page
value Parameter
value Parameter

D s
snippetusage

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

group

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. 

D s
snippetusage

Required?Data Type
NoString (column name)


D s
snippetExamples

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

Include Page
EXAMPLE - Replacement Transforms
EXAMPLE - Replacement Transforms

Example - Using $col placeholder

Include Page
EXAMPLE - Conditional Calculations Functions
EXAMPLE - Conditional Calculations Functions

D s also
labelwrangle_transform_set