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 transformation:

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 valuediscountRate
Big Co0.2
Little Guy Ltd0.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.

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.

LogicTestSendCheckinEmail
iftotalOrdersQ3 < 10true
if above is falselastOrderDays > 60true
if above is falsewrite defaultfalse


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'

TokenRequired?Data TypeDescription
caseYtransformName of the transform
ifNstring(For single if/then/else) Expression that is tested must evaluate to true or false.
thenNstring(For single if/then/else) Value written to the new column if the if expression is true.
elseNstring(For single if/then/else) Value written to the new column if the if expression is false.
colNstring(For single-column case) Name of column whose values are to be tested.
colCasesNcomma-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
casesNcomma-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.
defaultNany(For single-column case and custom condition case) If no matches are made, this value is written to the new column.
asYstringName 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 typeString (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 typeString 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 typeString 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 typeString (column name)

colCases

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

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

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

cases

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

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

Required?Data Type
Required for single-case condition typeArray (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 typeLiteral of any data type

as

Required?Data Type
YesString (column name)


See above.