Tuesday, December 18, 2012

If the source has duplicate records as id and name columns, values: 1 a, 1 b, 1 c, 2 a, 2 b, the target should be loaded as 1 a+b+c or 1 a||b||c, what transformations should be used for this?


If the source has duplicate records as id and name columns, values: 1 a, 1 b, 1 c, 2 a, 2 b, the target should be loaded as 1 a+b+c or 1 a||b||c, what transformations should be used for this?
                                                                                                                                                                    
Scenario:

If the source has duplicate records as id and name columns, values: 1 a, 1 b, 1 c, 2 a, 2 b, the target should be loaded as 1 a+b+c or 1 a||b||c, what transformations should be used for this?

Solution:

Follow the below steps - smiler exp
user a sorter transformation and sort the data as per emp_id
Use Exp transformation:
Create blow ports

V_emp_id = emp_id
V_previous_emp_id = emp_id
V_emp_name = emp_name
V_emp_full_name = iif(V_emp_id = V_previous_emp_id , V_emp_name||’ ’||                     V_emp_full_name, V_emp_name)
O_emp_full_name = V_emp_full_name
O_counter = iif(O_counter is null,1,O_counter+1)

output will look like

emp_id emp_name                                          Counter
101        soha                                                      1
 101       soha  ali                                                2
101        soha  ali kahn                                        3
102        Siva                                                      4
102        Siva shanker                                         5
102        Siva shanker Reddy                              6

Send Emp_id and Counter to Agg, where take a max counter for each id so o/p will be
Emp_id  Counter
101                3
102                6

5. Join output of step three and 4, you will get desire output as
Emp_id  Emp_name
101                 Soha ali Kahn
102                 Siva shanker Reddy

No comments:

Post a Comment

Thank you :
- kareem