Home > Software > Data-Warehouse > Informatica
Interview Questions   Tutorials   Discussions   Programs   

Informatica - How to join the two flat files without using joiner and lookup?




889
views
asked marvit September 20, 2014 08:03 AM  

How to join the two flat files without using joiner and lookup?


           

1 Answers



 
answered By vishnoiprem   0  
We can achieve this as below flow:

SRC-->SQ-->
              UNION-->AGG-->TGT
SRC-->SQ-->
 
In aggregator group by ID which we need to join

e.g.

Consider you have two files with data as follows
 
Flat-file 1        
emp_no ,name,sal
393,abc,100                             
394,xyz,200                      &nb sp;     
 
flat-file 2
emp_no,dept_no
393,30SU
394,29MK
 
in first pipe line expression  create one out put  port
out_deptno
give null value.
 
in second pipe line expression   create two  out put  ports
out_name
out_sal
give null value.
 
in union now tow groups with same number of attributes from both pipe lines.
output
EMPNO
NAME
SAL
DEPT_NO
 
Gropupone
pipe1_EMPNO
pipe1_NAME
pipe1_SAL
pipe1_DEPT_NO
 
Gro puptwo
pipe2_EMPNO
pipe2_NAME
pipe2_SAL
pipe2_DEPT_NO
 
after this create aggregator transformation for this in put is union transformation output group
EMPNO
NAME
SAL
DEPT_NO
 
now group by empno
 
the data from union to aggregator is
emp_no ,name,sal,dept_no
393,abc,100, null                 
394,xyz,200 ,null
393,null,null,30SU
394,null,null,29MK
 
now because of group by in aggregator
 
for each emp id there will be one record out put and create four out put ports in aggregator and for each out put port  expression should be max
out_SAL
out_NAME
out_DEPT
EMPNO
 
out put will be
emp_no ,name,sal,dept_no
393,abc,100,30SU
394,xyz,200 ,29MK
flag   
   add comment

Your answer

Join with account you already have

FF

Preview


Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!

Alert