Description

The Filter Rows step allows you to filter rows based on conditions and comparisons. Once this step is connected to a previous step (one or more and receiving input), you can click on the "<field>", "=" and "<value>" areas to construct a condition.

To enter an IN LIST operator, use a string value separated by semicolons. This also works on numeric values like integers. The list of values must be entered with a string type, e.g.: 2;3;7;8

Options

Option	Description
Step name	Optionally, you can change the name of this step to fit your needs.
Send 'true' data to step	The rows for which the condition specified is true are sent to this step
Send 'false' data to step	The rows for which the condition specified are false are sent to this step
The Condition

Click the NOT to negate the condition.

Click <Field> to select from a list of fields from the input stream(s) to build your condition(s). <value> to enter a specific value into your condition(s).

To delete a condition, right-click and select Delete Condition.
|

Add Condition

Click (Add condition) to add conditions. Add condition converts the original condition into a sub-level condition. Click a sub-condition to edit it by going down one level in the condition tree.

Filtering

Filtering Rows Based on Values from Variables

The filter rows step detects only fields in the input stream. If you want to filter rows based on a variable value, you must modify the previous step (a table input for example) and include the variable as another field, for example:

${myvar}=5

A query:

SELECT field1,
field2,
${myvar} AS field3 
FROM table
WHERE field1=xxxx

Then in the filter row condition, you can have

field1 = field3

FYI - instead of the Table Input trick above you can use the simple "Get Variables" step to set parameters in fields.

Filtering Special Characters

To filter special characters like explicit EOF (e.g. from old cobol files) Use a REGEX expression in the "filter row" step with the syntax: "\x{1A}" where \x mean HEX representation and 1A into parenthesis is the EOF char to match in HEX.

Metadata Injection Support (7.x and later)

All fields of this step support metadata injection. You can use this step with ETL Metadata Injection to pass metadata to your transformation at runtime.

Special considerations for the Condition field:

The Filter Rows step is a special MDI scenario, since it has a nested structure of filter conditions. The condition is given in XML notation. The condition XML has the same format as we store the transformation meta data in a KTR file in XML format. We do not have a DTD (Document Type Definition) for the KTR XML format, nor the condition.

It is easy to get to a XML condition:

Create a sample Filter step with the different conditions you need. This gives you all the information, for example the values for functions you use.
Select the step and Copy it to the clipboard. Paste it into a text editor. Alternatively you can store the KTR and open the KTR in a text editor.
Find the <condition> element and their nested elements and modify it accordingly to use it in your MDI scenario.