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 2: Lets 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

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

Click here to read the next scenario - Getting the value from the next row in the current 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.

  • One Stop to SAP BODI/BODS

    BODI Business Objects Data Integrator or BODS Business Objects Data Services is a GUI workspace that allows to create jobs that extracts data from heterogeneous sources, transforms that data using built-in transforms and functions to meet business...

  • RANK in Data Services

    In this article, we will learn how to implement RANK operation in SAP Data Services.

  • SAP Data Services Custom Function

    We can create our own functions by writing script functions in SAP Data Services scripting language using the smart editor. User-script functions return a value.

  • Data Services Scenario Questions Part 7

    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.

  • Working with Data Services Flatfiles

    In this article we will try to cover all the possible scenarios related to flatfiles in SAP Data Services.

  • Learning SAP Data Services - Online Tutorial

    We are pleased to start this new tutorial page for SAP BusinessObjects Data Services (BODS). If you do not know SAP Data Services (BODS) yet but wish to master this ETL tool, you have come to the right place. Even if you are already familiar with...

  • SAP Data Services Analytic Functions

    In this tutorial we will learn how to implement Cumulative Sum in SAP Data Services (BODS). Since there is no available in-built Analytic Functions in SAP Data Services, we will achieve the end result by exploiting some in-built Data Services...

  • Fools Guide to BODS - Introductory Tutorial

    This article is a guide to the readers who are totally ignorant about the tool SAP Business Objects Data Services. It is presented in a very simple way so that readers would be familiar with common terms, terminologies and would be able to run...

  • Data Services Scenario Questions Part 1

    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.

  • Data Services Scripting Language

    We can use the Data Services Scripting Language to write scripts and custom functions to address complex logical expressions.