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

Tuesday, 26 May 2015

Integration work out : USE CASE solved with Switch/Case, Merge Join , If field value is Null , Calculator Steps in Pentaho Data Integration

Hi ,

This post will explain the usage of various components with a USE CASE from community threads.

USE CASE thread : 
URL : 
http://forums.pentaho.com/showthread.php?187380-Data-need-to-be-filtered-and-sum-market-value-and-shares-based-on-Transaction-value&p=408478&posted=1#post408478

USE CASE : 
(A CSV file tab separated input).
Entity_Id Account_Name Trans_Code Total_Share Market_Value
10001 Growth Fund Buy 100 1000
10001 Growth Fund Sell 100 1000
10002 Aggressive Fund Buy 1300 13000
10002 Aggressive Fund Sell 450 4500
10003 Income Fund Buy 250 2500
10004 Brokerage Fund Buy 200 2000
10004 Brokerage Fund Sell 100 1000

USE CASE OUTPUT ( Subtraction for Total_Share(Buy-Sell) & Subtraction for Market_Value(Buy-Sell). 

Entity_Id Account_Name Total_Share Market_Value
10001 Growth Fund
10002 Aggressive Fund   850          8500
10003 Income Fund           250          2500

10004 Brokerage Fund  100                 1000

Design flow of ETL 

Quick understanding of steps used in above USE CASE 

1) Switch/Case
Detailed Information on Switch/Case :
http://wiki.pentaho.com/display/EAI/Switch-Case)


2) Multi way Merge Join
Detailed Information at :
http://wiki.pentaho.com/display/EAI/Multiway+Merge+Join )


3) If field value is Null
Detailed Information at :
http://wiki.pentaho.com/display/EAI/If+field+value+is+null )


4) Calculator
Detailed Information at :
(http://wiki.pentaho.com/display/EAI/Calculator)

Download files : 
( Input TEXT FILE + .ktr file) - Click Me

NOTE : 
Edit the input file path in .ktr file as per your system environment and explore it by running the file.

I hope this post has given some idea on steps subjected by seeing at the images provided.

What if there are more than 1 row with the same Id for sells or buys ? 
Will this procedure works ? ? NO

Write your comments for any queries related..

- Sadakar Pochampalli