Scenario 23: How to Join two tables without using Joiner, Source Qualifier and Lookup. (Level 1)

Scenario 23: How to Join two tables without using Joiner, Source Qualifier and Lookup. (Level 1)

CASE 1
For Employees data you are getting EMPNO, ENAME, JOB, MGR in one table let's say EMP1 and EMPNO, SAL, COMM, DEPTNO in another table let's say EMP2. 

CASE 2
Joining EMP and DEPT table according to NORMAL Join
http://informaticachamp.blogspot.in/2014/06/scenario-24-how-to-join-two-tables.html

Theory: 
Few days back we saw how to do union without using union transformation. (http://informaticachamp.blogspot.in/2014/06/scenario-21-how-to-do-union-without.html).  We have to reverse that logic. Means here we will do union for both tables then to avoid null we will use Aggregator.

Practically:
For doing Practical I am taking same case which I have explained in example.

Source
TABLE 1 = EMP 1                                                            TABLE 2 = EMP 2

EMPNO   Number                                                            EMPNO    Number
ENAME   Varchar2(10)                                                    SAL           Number
JOB          Varchar2(9)                                                      COMM     Number
MGR         Number                                                            DEPTNO  Number

Target

EMPNO   Number
ENAME   Varchar2(10)
JOB     Varchar2(9)
MGR     Number
SAL     Number
COMM    Number
DEPTNO  Number

Step 1:
Drag and Drop your both source and target table.

Step 2:
Expression
Drag and Drop column from Source Qualifier EMP1
Create three ports
SAL (Output Port) = NULL
COMM  (Output Port) = NULL
DEPTNO  (Output Port) = NULL

Step 3
Expression
Drag and Drop column from Source Qualifier EMP2
Create three ports
ENAME  (Output Port) = NULL
JOB  (Output Port) = NULL
MGR  (Output Port) = NULL

Step 4
Union
Create Two Groups in Group Tab (EMP1 and EMP2)
Connect Ports from Expression 1 and Expression 2 to each group.

Step 5
Aggregator
Create Six Ports

ENAME   (Output Port) = MAX(ENAME)
JOB     (Output Port) = MAX(JOB)
MGR    (Output Port) = MAX(MGR)
SAL    (Output Port) = MAX(SAL)
COMM   (Output Port) = MAX(COMM)
DEPTNO (Output Port) = MAX(DEPTNO)

Select EMPNO as GROUP port. 

Step 6
Connect your Aggregator to Target.

BINGO!!!

Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com