Use of a Sorter Tranformation

Suppose you have an requirement wherein, source contains Resource Id, Resource Name and the Mobile Brand name which he uses.

In a target table, you have 2 tables, one is resource_master and other is mobile_master.

In resource_master you have information like resource_id, resource_Name, and mobileBrand_id which he uses.

In mobile_master table you wil have mobileBrand name and mobileBrandId which is a refresesce to mobile_master table.

In Mobile_Master table, you will have unique entry for mobileBrand Name and its ID, when you join mobile_master and resource_master, you should get resource and name of the mobile which he useds.

Below are the db scripts which we will be using.



create table SourceTable(ResourceId number,ResourceName varchar2(200),MobileBrand
)


Insert into HR.SOURCETABLE
   (RESOURCEID, RESOURCENAME, MOBILEBRAND)
 Values
   (1, 'staksale', 'I-Phone');
Insert into HR.SOURCETABLE
   (RESOURCEID, RESOURCENAME, MOBILEBRAND)
 Values
   (2, 'asjaiswa', 'nokia');
Insert into HR.SOURCETABLE
   (RESOURCEID, RESOURCENAME, MOBILEBRAND)
 Values
   (3, 'amjadhav', 'samsung');
Insert into HR.SOURCETABLE
   (RESOURCEID, RESOURCENAME, MOBILEBRAND)
 Values
   (3, 'sgakhar', 'nokia');
Insert into HR.SOURCETABLE
   (RESOURCEID, RESOURCENAME, MOBILEBRAND)
 Values
   (4, 'magogate', 'nokia');
Insert into HR.SOURCETABLE
   (RESOURCEID, RESOURCENAME, MOBILEBRAND)
 Values
   (5, 'pkhvrk', 'LG');
Insert into HR.SOURCETABLE
   (RESOURCEID, RESOURCENAME, MOBILEBRAND)
 Values
   (6, 'venkk', 'LG');
COMMIT;

create table MobileMaster(MobileBrand varchar2(200),MobileBrandId number)create table ResourceMaster(ResourceId number,ResourceName varchar2(200),MobileBrandId number)

Besides are the result which we are expecting after running the mapping.

Below are the challenges
1. Assigning unique id to each mobile brand
2. Passing corresponding mobile brand id to both the tables.




Below is the mapping logic which we have used to populate the records to 2 separate target tables.

1. Here, after fetching the records from source we have first passed it to sorter and sorted them on the basis of mobile brand name.
2. Now once mobile brands are sorted, next step is to assign unique id to each brand name. For that we implemented below logic
To Identify if brand name got changed
V_CHANGE = IIF(MOBILEBRAND = V_OLDMOBILE_BRAND,0,1)
V_OLDMOBILE_BRAND = MOBILEBRAND
To assign Unique Id
OLDVAL = IIF(V_CHANGE=1,NEXTVAL,OLDVAL)
OUTVAL will be passed to both target

Now, V_Chage variable will have value 1 when there is a change in MobileBrand value ; otherwise it will be 0 always.

Note: V_Change and V_OldMobile_Brand port have specified order. If you move the port V_OldMobile_Brand before V_Change then result will be in-correct.

We need to pass new value only in case new mobile brand name appears. That we will come to know from value of v_Change varialbe. So, if V_Change value is 1 we will pass nextval of sequence or else OldVal.

OldVal we will store as "IIF(V_CHANGE=1,NEXTVAL,OLDVAL)"; so if change found at brandName new sequence value will be passed or else same old value.

2. Second challenge is to pass same value to other target table. Now if we simply connect brand name and brand id ports to other target tables, there will be repeated values at other tables.
So, we put aggregator in-between and aggregate the values on the basis of brandname. Now, only 1 value for each brand will get forwarded ahead.