Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
former_member182302
Active Contributor

Hi Folks,

In this blog, i will be explaining on how to achieve "Custom" Ranking using Graphical Calculation View.

Problem Description:

We have to show Top '2' ( Depends on the input from customer) Employees by "Salary" wise for each "Region" .i.e Top 2 employees in each region who are earning more salaries :smile:

**Note: if any body has the same salary they will share the same rank and they get ranked on the order of highest salary.

Example: Let us take the same table (Employee table) i have used in my previous blogs

SAP HANA: Using "Dynamic Join" in Calculation View (Graphical)

Using Script basec Calculation View using SQL Script:

We can achieve this very easily using window functions using Script based Calculation View as shown below:

SELECT "REGION","EMPLOYEE NAME","SALARY" FROM

(SELECT "REGION","EMPLOYEE NAME","SALARY",RANK() OVER (PARTITION BY "REGION" ORDER BY SALARY DESC) AS RANK

FROM EMPLOYEE)

WHERE RANK <= 2 ( we can use input parameter to make this dynamic)

Have achieved the same intended result using Graphical Calculation View as well.Please note that we cannot use "Rank" function in the graphical calculation view ( atleast in the current version it is not supported :smile: )

Using Graphical Calculation View:

Please find the snapshot of the model created below:

Have a look on this document for further details on creating it.

SAP HANA: Implementing RANK using Graphical calculation View

Output:

As seen above, based on the input you give ex: 2, you will get Top 2 Employees ranked based on their salaries.

Using Script based Calculation Views using CE Functions:

As it would be difficult to show all the nodes and their screenshots , i have recreated the same logic using "CE" functions as shown below:

/********* Begin Procedure Script ************/

BEGIN

t = CE_COLUMN_TABLE(KRISHNA.EMPLOYEE);

t1 = CE_PROJECTION(:t,[CE_CALC('1',int) as dummy, "REGION","EMPLOYEE NAME", "SALARY"]);

t2 = CE_PROJECTION(:t,[CE_CALC('1',int) as dummy, "REGION","EMPLOYEE NAME" AS "EMPLOYEE NAME1", "SALARY" as SALARY2]);

t3 = CE_JOIN(:t1, :t2, [dummy,REGION]);

t4 = CE_AGGREGATION(:t1,[sum(dummy) as "count"],[dummy,"REGION"]);

t5 = CE_PROJECTION(:t4,["REGION","count",dummy]);

t6 = CE_JOIN(:t5, :t3, [dummy,REGION],["count",dummy,"REGION","EMPLOYEE NAME","EMPLOYEE NAME1","SALARY","SALARY2"]);

t7 = CE_PROJECTION(:t6, ["REGION","EMPLOYEE NAME","EMPLOYEE NAME1", "SALARY", SALARY2,dummy,"count"],'"SALARY2" <= "SALARY"');

t8 = CE_AGGREGATION(:t7,[count("EMPLOYEE NAME") as rank1],["REGION","EMPLOYEE NAME", "SALARY","count"]);

t9 = CE_PROJECTION(:t8, ["REGION","EMPLOYEE NAME", "SALARY", rank1,"count",CE_CALC('"count"-"RANK1"+1',int) as rank]);

var_out =  SELECT REGION,"EMPLOYEE NAME" AS "EMPLOYEE_NAME",SALARY,rank as "rank" FROM :t9;

END /********* End Procedure Script ************/

Hence we saw the 3 ways in which we can get the ranking i.e Calc Script using SQL Vs Calc Graphical Vs Calc Script using CE functions.

Please do let me know your feedback on this :smile: .

Your's

Krishna Tangudu :smile:

10 Comments
Labels in this area