hop-user-manual/modules/ROOT/pages/plugins/transforms/filterrows.adoc

////
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements.  See the NOTICE file
distributed with this work for additional information
regarding copyright ownership.  The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License.  You may obtain a copy of the License at
  http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied.  See the License for the
specific language governing permissions and limitations
under the License.
////
:documentationPath: /plugins/transforms/
:language: en_US
:page-alternativeEditUrl: https://github.com/apache/incubator-hop/edit/master/plugins/transforms/filterrows/src/main/doc/filterrows.adoc
= Filter Rows

== Description

The Filter Rows transform allows you to filter rows based on conditions and comparisons. Once this transform is connected to a previous transform (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

[width="90%", options="header"]
|===
|Option|Description
|transform name|Optionally, you can change the name of this transform to fit your needs.
|Send 'true' data to transform|The rows for which the condition specified is true are sent to this transform
|Send 'false' data to transform|The rows for which the condition specified are false are sent to this transform
|The Condition|
|Add Condition| Click 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 transform detects only fields in the input stream. If you want to filter rows based on a variable value, you must modify the previous transform (a table input for example) and include the variable as another field, for example:

[source,bash]
----
${myvar}=5
----

A query:

[source,sql]
----
SELECT field1,
field2,
${myvar} AS field3 
FROM table
WHERE field1=xxxx
----

Then in the filter row condition, you can have

[source,bash]
----
field1 = field3
----

*FYI* - instead of the Table Input trick above you can use the simple "Get Variables" transform 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" transform 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 

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

== Special considerations for the condition field

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

It is easy to get to an XML condition:

1. Create a sample Filter transform with the different conditions you need. This sample transform gives you all the information, such as the values for the functions you use.
2. Select the transform, copy it to the clipboard, and then paste it into a text editor. Alternatively, you can store the .HPL, and then open the .HPL in a text editor.
3. Find the <condition> element and its nested elements and modify it accordingly to use it in your MDI scenario.