Pages

Sunday, March 17, 2013

How to split the non-key columns to separate tables with key column in both


Split the non-key columns to separate tables with key column in both  / How to split the data of source table column-wise with respect to primary key. See the source and target tables below.
source table: ID is the key column, Name and Phone No are non-key columns 
IDNamePhone No
10AAA123
20BBB234
30CCC434
40DDD343
50EEE442

Target Table 1:
IDName
10AAA
20BBB
30CCC
40DDD
50EEE
Target Table 2:
IDPhone No
10123
20234
30434
40343
50442
Solution:
Step  1: Source qualifier: get the source table to the mapping area. See image below.
scenario 2 src def to src qualifier
Step 2:  Drag all the port from (from the previous step) to the Aggregator transformation and group by the key column. Since we have to split the columns to two different tables with the key column in each, so we are going use two expression transformation, each will take the key column and one non-key column. Connect  aggregator transformation with each of the expression transformation as follows.
scenario 2 agg to expression
Step 3: We need another set of  aggregator to be associated with each of the expression tranformation from the previous step.
Step 4: In the final step connect the aggregators with the two target tables as follows.
scenario 2 agg to tgt
Here is the iconic view of the entire mapping.
scenario 2 iconic view of mapping
Comments are appreciated

No comments:

Post a Comment