| //// |
| 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. |