Case

The Case processor returns values based on a sequence of conditions, like a cascading if-then-else statement.

When you configure a Case processor, you define one or more sets of conditions and return values. You specify the conditions in the order that you want them evaluated.

When appropriate, you can define a SQL expression to use with all specified conditions. You can optionally define an Else property that applies to all other data. Then, you specify the output column for all return values.

Example

Your company wants to send shirts to employees that will work at a conference booth. Different departments are to receive different shirts. To do this, you might configure the Case processor as follows:
  • Conditions:
    When Output
    (DEPT = 'sales') OR (DEPT = 'marketing') 'oxford'
    (DEPT = 'eng') 't-shirt'
  • Else: send no shirts
  • Output Column: SHIRTS

With the following incoming data:

EMP_ID DEPT LOCATION
101 sales NY
102 marketing NY
103 hr NY
104 eng CA
105 eng CA
106 sales CA

The processor passes the following output downstream:

EMP_ID DEPT LOCATION SHIRTS
101 sales NY oxford
102 marketing NY oxford
103 hr NY send no shirts
104 eng CA t-shirt
105 eng CA t-shirt
106 sales CA oxford

Notice how the marketing and sales teams receive oxford shirts and the engineering team gets t-shirts. Since the HR team does not work at the conference, they do not receive shirts.

Note that if you had included marketing in the second condition, the results from the processor would remain the same. This is because the processor evaluates conditions in the specified sequence, and marketing is evaluated with the first condition.

Using a SQL Expression

When appropriate, you can configure the Case processor to use a SQL expression with the defined conditions.

When you configure a SQL expression, the processor prepends the expression to all defined conditions, as follows:
<expression> = <condition>
As a result, you should define an expression only under the following conditions:
  • The expression applies to all conditions defined in the When properties.
  • The expression uses the equals operator ( = ) with the defined conditions.
Note: Do not include equals operator in the expression. The processor includes the operator when building the SQL query.

Example

In the example above, the Case processor is configured as follows:
  • Conditions:
    When Output
    (DEPT = 'sales') OR (DEPT = 'marketing') 'oxford'
    (DEPT = 'eng') 't-shirt'
  • Else: send no shirts
  • Output Column: SHIRTS
Because the conditions all compare using the equals operator and evaluate values in the DEPT column, you can configure the processor as follows to achieve the same results:
  • Use Expression: Enabled
  • Expression: DEPT
  • Conditions:
    When Output
    'sales' 'oxford'
    'marketing' 'oxford'
    'eng' 't-shirt'
  • Else: send no shirts
  • Output Column: SHIRTS

Configuring a Case Processor

Configure a Case processor to return values based on a sequence of conditions.

  1. On the General tab, configure the following properties:
    General Property Description
    Name Stage name.
    Description Optional description.
    Cache Data Caches processed data.
  2. On the Case tab, configure the following properties:
    Case Property Description
    Use Expression Enables specifying a SQL expression. Only use an expression under the following conditions:
    • The expression applies to all conditions defined in the When properties.
    • The expression uses the equals operator ( = ) with the defined conditions.
    Expression SQL expression to prepend to the defined conditions.

    Do not include the equals operator in the expression. The processor includes the operator when building the SQL query.

    Conditions Define the following properties for each condition that you want to use:
    • When - Condition to evaluate.
    • Output - Output value when the condition evaluates to true.

    Click Add Another to specify another condition.

    Multiple conditions are processed in the order that they appear.

    Else Optional value to use when none of the conditions are met.

    If you do not specify a value, rows that do not match the defined conditions receive a null value in the output column.
    Output Column Output column for the output values defined in the Output and Else properties.