Case Transform
Nota
Transforms are a part of the underlying language, which is not directly accessible to users. This content is maintained for reference purposes only. For more information on the user-accessible equivalent to transforms, see Transformation Reference.
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:
Basic Usage
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 |
Syntax and Parameters
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 |
then | N | string | (For single if/then/else) Value written to the new column if the if expression is |
else | N | string | (For single if/then/else) Value written to the new column if the if expression is |
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:
|
cases | N | comma-separated arrays | (For custom conditions case) Matrix of expression-value pairs:
|
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. |
For more information on syntax standards, see Language Documentation Syntax Notes.
if
For if-then-else condition types, this value is an expression to test. Expression must evaluate to true
or false
.
Usage Notes:
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
.
Usage Notes:
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
.
Usage Notes:
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.
Usage Notes:
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.
Usage Notes:
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
orfalse
.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.
Usage Notes:
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.
Usage Notes:
Required? | Data Type |
---|---|
Required for single-case condition type | Literal of any data type |
as
Name of the new column that is being generated. If the as
parameter is not specified, a default name is used.
Usage Notes:
Required? | Data Type |
---|---|
Yes | String (column name) |
Examples
Sugerencia
For additional examples, see Common Tasks.
See above.