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

RANK in Data Services

 
Updated on Oct 01, 2020

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

Implementation

Let us take the Employee table as Source. We want to RANK all the employees based on their salary. So the Employee with the highest salary will get first rank and the employee with lowest salary will get the last rank. Now in the event when many employees have the same salary then they basically share the same rank. But in case of normal ranking the immediate next record will not get the next consecutive rank. So eventually there will be gaps in RANKS generated.

1. First we create a Batch Job, say Job_Rank_Generation. Within the Job we create a Data Flow, say Df_Rank_Generation.

2. Next we create a New Custom Function from the Local Object Library. Let's name it CF_RANK_GENERATION.

Custom Function
Custom Function

Within the Custom Function Smart Editor, we define the Parameters and Variables as below:

Param/VariableNameDatatypeType
ParameterReturnintOutput
Parameter$PREV_SALdecimal(7,2)Input
Parameter$CURR_SALdecimal(7,2)Input
Variable$HOLD_RANKint
Variable$FINAL_RANKint
Custom Function Definition
Custom Function Definition

3. Next we define the Custom Function CF_RANK_GENERATION as below and Validate the same.

if( $PREV_SAL IS NULL )
begin
  $FINAL_RANK = 1;
  $HOLD_RANK = 1;
  Return $HOLD_RANK;
end

else if( $PREV_SAL = $CURR_SAL )
begin
  $FINAL_RANK = $FINAL_RANK + 1;
  Return $HOLD_RANK;
end

else
begin
  $FINAL_RANK = $FINAL_RANK + 1;
  $HOLD_RANK = $FINAL_RANK;
  Return $HOLD_RANK;
end

4. Lets go back and design the Dataflow. First of all we take the EMP table, from the Local Object Library as Source.

Data flow
Data flow

5. Next we place a Query transform, say QRY_SORT_SAL_DESC to sort the employee data based salary in descending order. First we select all the columns from the Schema In of the Query transform and Map to Output. Next on the ORDER BY tab, select the EMP.SAL column of the Schema In and Sort Type as Descending

Query- Sort
Query- Sort

6. Next we place another Query transform, say QRY_SET_PARAM to set the Parameter values that will passed as arguments to the Custom Function to get the Ranking. First we select all the columns from the Schema In of the Query transform and Map to Output. Next we create a New Output Column, say PREV_SAL with Data type as decimal(7,2) to capture the previous row value of salary and define the Mapping as below:

previous_row_value( QRY_SORT_SAL_DESC.SAL )
Query- Set Parameter
Query- Set Parameter

7. Next we place another Query transform, say QRY_GEN_RANK to call the Custom Function with the input parameters that we have already designed in the previous transform. First we select all the columns except PREV_SAL from the Schema In of the Query transform and Map to Output. Call the Function with the Input Parameters as below. This function will eventually return the Rank for each record based on the salary.

Create a New Function Call in Schema Out of the Query transform. Choose the Custom Functions from the Function categories and select the Function name CF_RANK_GENERATION.

Next we Define Input Parameters. We specify the inputs as below:

$PREV_SAL = QRY_SET_PARAM.PREV_SAL

$CURR_SAL = QRY_SET_PARAM.SAL
Function Input Parameters
Function Input Parameters

Select the Return column as the Output Parameter.

Query- Rank
Query- Rank

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

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