Page tree

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

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:

D s
snippetBasic

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 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.

  • 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.

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


D s
snippetParameters

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'

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.

 

D s lang notes

if

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

D s
snippetusage

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

D s
snippetusage

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

D s
snippetusage

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.

D s
snippetusage

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.

  • 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.

D s
snippetusage

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.

  • 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.

D s
snippetusage

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.

D s
snippetusage

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

as

Include Page
as Parameter
as Parameter

D s
snippetusage

Required?Data Type
YesString (column name)


D s
snippetExamples

See above.

D s also
labelwrangle_transform_case