Splitting data with the Case transform

The Case transform supports separating data from a source into multiple targets based on branch logic.

Use the Case transform to simplify branch logic in data flows by consolidating case or decision–making logic into one transform, instead of designing several Query transforms with different filters.

You could use, for example, the Case transform to read a table that contains sales revenue facts for different regions. It separates the regions into their own tables for more efficient data access.

Case Transform Logic

Simplifies data flow branching logic:
- Uses a single transform
- Separates input data rows into multiple output data sets
Allows for simple or complex logic:
- Simple conditions on a single column: RegionID = 1
- Complex conditions on a single column: SUBSTR(RegionName, 1, 3) = 'Nor'
- Simple or complex conditions on multiple columns: RegionID = 1 OR SUBSTR("RegionName", 1, 3) = 'Nor'

Additional Features

Provides a default path to manage rows that do not meet any of the conditions.
Allows for rows to go to several targets if several conditions are met, depending on 'Row can be TRUE for one case only' option.
Can preserve expression order, or not, if rows can be TRUE for one case only.

The Case Transform Editor

The Case transform editor allows you to create the required conditions.

Each condition is represented by a label and an expression. Labels are used to map the condition to the corresponding target table.

Default output is similar to CASE...ELSE (or OTHERWISE). If there is no default output, rows that do not meet any conditions will be dropped.

Enable preserve expression order, when the row is [can be] true for one case, to control the sequence of the test. Otherwise tests are performed in an order determined by the Data Services optimization engine, and might not concur with desired business rules.

Case Transform Requirements

Only one source is allowed.
The source structure is not modified.
Each output label in the Case transform must be used at least once.

Let's Try It

Let me guide through the use of a Case transform:

ExerciseStart Exercise