View Source

Performs conditional transformation of data with a single statement using if-then-else logic or with multiple statements using case logic. Results are generated in a new column.

There are function equivalents to this transform:

Example - if/then/else

This example illustrates a single if/then/else construction:

case if: testScore >= 60 then: 'yes' else: 'no' as: 'passedTest'

Output: If a value in the testScore is greater than or equal to 60, a value of yes is written into the new passedTest column. Otherwise, a value of no is written.

Example - Case (single column)

This example shows how to step through a sequence of case tests applied to a single column.

case col: custName colCases: ['Big Co',0.2],['Little Guy Ltd',0.05] default: 0 as: 'discountRate'

Output: Checks names in the custName column and writes discount values based on exact matches of values in the column:

custName value	discountRate
`Big Co`	0.2
`Little Guy Ltd`	0.05
default (if no matches)	0

Example - Case (custom conditions)

The following example illustrates how to construct case transforms with multiple independent conditions. Tests can come from arbitrary columns and expressions.

The first case is tested:
- If true, then the listed value is written to the new column.
- If false, then the next case is tested.
If none of the stated cases evaluates to true, then the default value is written.

case cases: [totalOrdersQ3 < 10, true], [lastOrderDays > 60, true] default: false as: 'sendCheckinEmail'

Output: If the total orders in Q3 < 10 OR the last order was placed more than 60 days ago, then write true in the sendCheckinEmail. Otherwise, write false.

Logic	Test	SendCheckinEmail
if	`totalOrdersQ3 < 10`	`true`
if above is false	`lastOrderDays > 60`	`true`
if above is false	write default	`false`

case [if: if_expression] [then:'str_if_true'] [else:'str_if_false] [col:col1] [colCases: [[Match1,Val1]],[[Match2,Val2]] [cases: [[Exp3,Val3]],[[Exp4,Val4]] [default:default_val] as: 'new_column_name'

Token	Required?	Data Type	Description
case	Y	transform	Name of the transform
if	N	string	(For single if/then/else) Expression that is tested must evaluate to `true` or `false`.
then	N	string	(For single if/then/else) Value written to the new column if the if expression is `true`.
else	N	string	(For single if/then/else) Value written to the new column if the if expression is `false`.
col	N	string	(For single-column case) Name of column whose values are to be tested.
colCases	N	comma-separated arrays	(For single-column case) Matrix of string-value pairs: First entry is the value to match. Second entry is the value written to the new column if a match appears
cases	N	comma-separated arrays	(For custom conditions case) Matrix of expression-value pairs: First entry is the expression to evaluate. Second entry is the value to write if the expression is `true`.
default	N	any	(For single-column case and custom condition case) If no matches are made, this value is written to the new column.
as	Y	string	Name of the new column where results are written.

if

For if-then-else condition types, this value is an expression to test. Expression must evaluate to true or false.

Required?	Data Type
Required for if-the-else condition type	String (expression)

then

For if-then-else condition types, this value is a literal value to write in the output column if the expression evaluates to true.

Required?	Data Type
Required for if-the-else condition type	String or other literal type

else

For if-then-else condition types, this value is a literal value to write in the output column if the expression evaluates to false.

Required?	Data Type
Required for if-the-else condition type	String or other literal type

col

For single-case condition types, this value identifies the column to test.

Required?	Data Type
Required for single-case condition type	String (column name)

colCases

For single-case condition types, this parameter contains a comma-separated set of two-value arrays.

Array value 1: A literal value to match in the specified column.
Array value 2: If the value is matched, this value is written into the output column.

You can specify one or more cases as comma-separated two-value arrays.

Required?	Data Type
Required for single-case condition type	Array (comma-separated list)

cases

For multi-case condition types, this parameter contains a comma-separated set of two-value arrays.

Array value 1: An expression to test, which must evaluate to true or false.
Array value 2: If the value is matched, this value is written into the output column.

You can specify one or more cases as comma-separated two-value arrays.

Required?	Data Type
Required for single-case condition type	Array (comma-separated list)

default

For single-case and multi-case condition types, this parameter defines the value to write in the new column if none of the cases yields a true result.

Required?	Data Type
Required for single-case condition type	Literal of any data type

as

Required?	Data Type
Yes	String (column name)

See above.