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

1. user a sorter transformation and sort the data as per emp_id
2. 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)

3. 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

4. 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. Joint output of step three and 4, you will get desire output as

Emp_id  Emp_name

101                 Soha ali Kahn

102                 Siva shanker Reddy