Data Services Scenario Questions Part 7

This article is one of the series of articles written to showcase the solutions of different business scenarios in SAP Data Services. You may browse all the scenarios from the below list.

1. Cumulative Sum of salaries, department wise
2. Getting the value from the previous row in the current row
   
3. Getting the value from the next row in the current row
   
4. Getting total Sum of a value in every row
   
5. Cumulative String Concatenation (Aggregation of string)
   
6. Cumulative String Aggregation partition by other column
   
7. String Aggregation
   

Scenario 7: With reference to the previous department-employee source file, lets try to load the target table data as below:

Solution:

The approach to this Scenario will be exactly the same as that of Scenario 6, discussed in the previous section. First take a look at the Data flow below:

The difference lies in the last three Query transforms namely- QRY_FORMAT, QRY_REV_SORT and QRY_FILTER. Apart from that the Data flow is an exact replica of the previous scenario solution.

In Query transform QRY_FORMAT we define a New Output Column, say SEQ_NUM_GRP with data type int and specify the Mapping as gen_row_num_by_group( QRY_CONCAT_NAME.DEPTNO ).

Using the Data Services function gen_row_num_by_group we generate sequence numbers starting from 1 for every departments.

Next in the Query transform, QRY_REV_SORT, select the columns DEPTNO and ENAME_LIST from the Schema In of the Query transform and Map to Output. Specify the ORDER BY on DEPTNO Ascending and SEQ_NUM_GRP in Descending type.

Finally in the Query transform, QRY_FILTER, we will filter only the first row of each department records coming from the previous transform. Select DEPTNO and ENAME_LIST from the Schema In of the Query transform and Map to Output. Specify the WHERE clause as is_group_changed( QRY_REV_SORT.DEPTNO ) = 1.

Using the Data Services function is_group_changed we will capture only the first record of each set of departments.

Finally we place a Template Table as Target in the Target Datastore.