| Excerpt |
|---|
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:
| D code |
|---|
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.
| D code |
|---|
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.
| D code |
|---|
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 |
| D code |
|---|
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. |
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) |
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 |
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 |
For single-case condition types, this value identifies the column to test.
| Required? | Data Type |
|---|
| Required for single-case condition type | String (column name) |
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) |
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) |
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 |
| Required? | Data Type |
|---|
| Yes | String (column name) |
See above.
| D s also |
|---|
| label | wrangle_transform_case |
|---|
|
