In this tutorial we will discuss some scenario based questions and their solutions using SAP Data Services. This article is meant mainly for Data Services beginners.

Scenario 3: Lets 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:

DEPTNOSALARYNEXT_SALARY
1010002000
2020003000
3030004000
404000NULL

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.

Data flow

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.

Sequence Number

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 .

Detail Pipeline

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.

JOIN- Master Detail

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.

Data Preview

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


Have a question on this subject?

Ask questions to our expert community members and clear your doubts. Asking question or engaging in technical discussion is both easy and rewarding.

Are you on Twitter?

Start following us. This way we will always keep you updated with what's happening in Data Analytics community. We won't spam you. Promise.