Sorting on similar values using talend

I have a CSV file

I want to group together similar DeptID and sort the MID in ascending order and assign the value of lowest MID to SID who have similar DeptID using talend open studio for data integration. If there is one DeptID, then assign the same value of MID to SID.

Input CSV :

I have used tFileInputDelimited to read the input file, I have used tSortRow to sort MID. And I have used tAggregateRow to group the values.

I am getting output as:

The Output CSV should be as follows:

One simple solution would be to read twice your input file : one time as the main flow, to get detail of MID column , one time as the lookup to get MIN value of MID column as your SID column. Then join the 2 flows with a tMap, joining on deptID (with "all matches" join type).

Another solution could be to use internal variables of tMap to get the work done with fewer components :

Once you have sorted your data with tSortRow, create 2 variables in a tMap following your tSort component :

• "sequence" creates an increment based on DeptId, starting at 1

• "currentVal" checks if sequence equals 1 : if so you get the current MID as SID. Else SID don't change.
  

The 2 solutions work to get the SID value .