Pentaho Tools :

Pentaho C-Tools(CDE,CDF,CDA),Pentaho CE & EE Server,OLAP-Cubes,Analysis using Pivot4J, Saiku Analytics, Saiku Reporting, Ad-hoc Reporting using Interactive Reporting Tool,Dashboards,Reports using PRD, PDD,Data Integration using Kettle ETL,Data Mining usign WEKA,Integration of Servers with Databases,Mobile/iPad compatible Dashboards using Bootstrap Css,Drilldown dashboards,Interactive Dashboards

Thursday 20 August 2015

Basics of writing AND and OR conditions in Filter rows Step in Pentaho Data Integration (aka Kettle ETL)

This post is intended to the beginners of Kettle ETL on writing AND and OR conditions in Filter Step 

Let us take this problem statement (taken from community pentaho forums).

Reference : 
http://forums.pentaho.com/showthread.php?192116-How-to-load-the-different-targets-in-PDI-based-on-multiple-condition

Target1 will have records where Salary < 50000 and Dept_id =10 or 20 and
Target2 will have records where Salary > 50000 and Dept_Id=30 or 40 and
Target3 will have the rows which don't satisfy Target-1 and Target-2 conditions.

Software used : Windows 10, PDI- 5.4 CE stable , Java 1.7 installed


Sample source data : 
Transformation Design  

Combination of AND and OR conditions in Filter rows step

To do the negative of the condition just click on empty condition box appeared left top corner.
You can find the "NOT" keyword which will negative the condition written. 

NOTE : send the true and false data to target steps as shown in above image. 

outputs  - 3 target outputs based on conditions we took in problem. 
Target-1 output : <50000 and 10 & 20 are as dept


Target-2 output : >50000 and 30 and 40 are as dept


 Target-3 : rows which don't satisfy the conditions of Target-1 and Target-2 

I hope this use-case helps some in understanding AND and OR conditions usage in Filter rows step in Kettle ETL 

Download Example : Don't wait - Click Me..!!!

- Sadakar Pochampalli

No comments:

Post a Comment