Data Services Scenario Questions Part 3

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 3: Let's try to design a dataflow to get the Next Departments Salary for the current Department. If there is no next row exists for the current row, then the next row salary should be displayed as null. The target table data should look like below:

Solution:

1. Let us reuse the File Format as defined in the previous examples as Source.

2. Next we use the same Batch Job, JB_SCENARIO_DS. Within the Job we create a Data Flow, say DF_SCENARIO_3.

3. Use File Format, from the Local Object Library as Source in the Data flow Workspace.

4. Next we place a Query transform, say QRY_SEQ_NUM. First we select the columns DEPTNO and SALARY from the Schema In of the Query transform and Map to Output.

Next we specify a New Output Column in Schema Out of the Query transform. Let's name it as SEQ_NUM with data type int.

Next we define the Mapping value as gen_row_number().

Using the Data Services function gen_row_number we can generate sequence number for each incoming records with a start value of 1.

5. Next we place two Query transform, say QRY_MASTER and QRY_DETAIL. For QRY_MASTER, select the columns DEPTNO, SALARY and SEQ_NUM from the Schema In of the Query transform and Map to Output.

For QRY_DETAIL, select the columns DEPTNO, SALARY and SEQ_NUM from the Schema In of the Query transform and Map to Output. Next change the Mapping value as QRY_SEQ_NUM.SEQ_NUM - 1.

6. Next we place another Query transform, say QRY_NEXT_SAL. Select the columns DEPTNO, SALARY of Input Schema QRY_MASTER from the Schema In of the Query transform and Map to Output. Also select the column SALARY of Input Schema QRY_DETAIL from the Schema In of the Query transform and Map to Output. Rename this Output column to NEXT_SALARY. QRY_MASTER is selected as source and we perform a LEFT OUTER JOIN with QRY_DETAIL based on the column SEQ_NUM.

Since for all the detail records the sequence number is decreased by 1, upon joining the first row of the master will join with the second row of the detail. Hence we will be able to capture the Next Salary for the currently processing Department row.

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

Click here to read the next scenario - Getting total Sum of a value in every row.