Logo DWBI.org Login / Sign Up
Sign Up
Have Login?
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
Login
New Account?
Recovery
Go to Login
By continuing you indicate that you agree to Terms of Service and Privacy Policy of the site.
SAP Data Services

Data Services Scenario Questions Part 3

 
Updated on Oct 02, 2020

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.

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:

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
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
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
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
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
Data Preview

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

PrimeChess

PrimeChess.org

PrimeChess.org makes elite chess training accessible and affordable for everyone. For the past 6 years, we have offered free chess camps for kids in Singapore and India, and during that time, we also observed many average-rated coaches charging far too much for their services.

To change that, we assembled a team of top-rated coaches including International Masters (IM) or coaches with multiple IM or GM norms, to provide online classes starting from $50 per month (8 classes each month + 4 tournaments)

This affordability is only possible if we get more students. This is why it will be very helpful if you could please pass-on this message to others.

Exclucively For Indian Residents: 
Basic - ₹1500
Intermediate- ₹2000
Advanced - ₹2500

Top 10 Articles