Mappings

Back Next

Step 3. Create the Filter transformation

In this step, you create a Filter transformation to filter customers based on the country where each customer is located. You also create a parameter to hold country values.

You want this mapping to run tasks that filter customers based on specific countries, so add a Filter transformation to the data flow to capture country information. Then define a parameter in the filter condition to hold the country value.

When you use a parameter, you can reuse the same mapping to create multiple tasks. You can enter a different country value for each task. Or you can use the same

mapping

task and change the value for country when you run the task.

The sample CUSTOMERS table includes a "country" field. When you use the country field in the filter condition, you can write data to the target based on the country. For example, when you use "country = France" as the filter condition, you include only customers based in France in the data flow. When you use a parameter for the value of the filter condition, you can define the country that you want to use when you run the task.

Field rules define the fields that enter the transformation and how they are named. By default, all available fields are included in the transformation. You might want to exclude unnecessary fields when you have large source tables. Or you might want to change the names of certain incoming fields, for example, when you have multiple sources in a mapping. Field rules are configured on the

Incoming Fields

tab. For this tutorial, you won't configure field rules.

Drag a Filter transformation from the transformation palette to the mapping canvas and drop it between the Source transformation and the Target transformation.

You might need to scroll through the transformation palette to find the Filter transformation.

When you drop a new transformation in between two transformations in the canvas, the new transformation is automatically linked in the data flow, as shown in the following image:

The Source, Filter, and Target transformations display in a row in the mapping canvas with arrows connecting the transformations, so the transformations are connected in the data flow.

When you link transformations, the downstream transformation inherits fields from the previous transformation.

Select the Filter transformation on the mapping canvas.

To name the Filter transformation, in the Properties panel, click

General

and enter

flt_FilterByCountry

as the transformation name.

To create a simple filter with a parameter for the value, click

Filter

. For the

Filter Condition

, select

Simple

Click

Add New Filter Condition

, as shown in the following image:

When you click

Add New Filter Condition

, a new row is created where you specify values for the new filter condition.

For

Field Name

, select

country

For

Operator

, select

Equals

To parameterize the filter condition value, for

Value

, select

New Input Parameter

In the

New Parameter

dialog box, configure the following options:

Filter Condition Detail	Description
Name	Name of the filter condition parameter. Enter p_FilterConditionValue .
Display Label	Label that shows in the mapping task wizard where you enter the condition value. Enter Filter Value for Country .
Description	Description that appears in the mapping task wizard. Enter this text: Enter the country name for the data you want to use.
Type	Data type of the field used for the filter condition. The country field is a String data type, which is already selected in the dialog box.
Default Value	Default value for the filter condition. The mapping task uses this value unless you enter a different value. You want to run the task for customers in the United States by default, so enter USA .

Click

. The new filter condition displays in the Properties panel, as shown in the following image:

The Filter Conditions table shows the completed filter condition, "country = USA."

To save your changes, click

Save

The mapping will still be marked "Invalid" at this point because you haven't configured the target yet.

Rename Saved Search

Table of Contents

Mappings

Mappings

Step 3. Create the Filter transformation

Step 3. Create the Filter transformation