Logo DWBI.org Login / Sign Up
Sign Up
Have Login?
Login
New Account?
Recovery
Go to Login
SAP Data Services

Data Services Scenario Questions Part 2

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

DEPTNOSALARYPREVIOUS_SALARY
101000NULL
2020001000
3030002000
4040003000

Solution:

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

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

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_PREV_SAL. 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 PREV_SALARY with data type decimal(10,2).

Next we define the Mapping value as previous_row_value( FF_SRC_DEPT.SALARY ).

Using the Data Services function previous_row_value we can capture the previous value of a column for the current record being processed.

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

Data Preview
Data Preview

Click here to read the next scenario -  Getting the value from the next row in the current row.