You are Here: ://SAP HANA – Rank Function in Calculation View

SAP HANA – Rank Function in Calculation View

Hello Everyone,

In this article, we are going to discuss about how to build rank function using graphical Calculation View in SAP HANA.

Before we go ahead and see how to build this, let’s look at details about RANK function.

RANK:

  • This function calculates the rank for a data set based on the partition and ordering clause.
  • This will be very useful when we have to pick latest records from multiple records or top N or Bottom N records from the source set.

SQL Syntax:

  • RANK() OVER (PARTITION BY <Colum Name> ORDER BY <Colum Name> ASC/DESC)

SAP HANA Supports RANK function from initial version – if we are going to use this as part of SQL Script, however from SAP HANA SP9 revision we have this functionality as an additional node while creating graphical calculation view.

 

Scenario:

  • The scenario here is, we have sales orders data, where in, for every change to existing sales order there will be a new record in the table.
  • When we build report on this table, we have to pick the latest sales order i.e. which has changed recently based on the ordered time.

Data Set: Please execute the below SQL statements, if you would like to practice the same exercise in your system.

Create Column Table Statement:

CREATE COLUMN TABLE SAP_STUDENT.ORDERS_DATA_RANK (ORDER_NO INTEGER,

                                                               CUST_NO NVARCHAR(10),

                                                               PROD_NO NVARCHAR(10),

                                                               QUANTITY INTEGER,

                                                               PRICE INTEGER,

                                                               ORDERED_TIME SECONDDATE)

 

Insert Data Into Table:

INSERT INTO SAP_STUDENT.ORDERS_DATA_RANK VALUES(1234,’C01′,’P012′,10,5,’2015-09-10 08:03:12′);

INSERT INTO SAP_STUDENT.ORDERS_DATA_RANK VALUES(1235,’C02′,’P023′,100,10,’2015-09-10 14:45:36′);

INSERT INTO SAP_STUDENT.ORDERS_DATA_RANK VALUES(1236,’C03′,’P067′,80,20,’2015-09-15 21:23:56′);

INSERT INTO SAP_STUDENT.ORDERS_DATA_RANK VALUES(1234,’C01′,’P012′,30,5,’2015-09-10 11:03:12′);

INSERT INTO SAP_STUDENT.ORDERS_DATA_RANK VALUES(1234,’C01′,’P012′,50,5,’2015-09-12 08:03:12′);

INSERT INTO SAP_STUDENT.ORDERS_DATA_RANK VALUES(1236,’C03′,’P067′,120,20,’2015-09-17 11:23:12′);

  • Once you run the above insert statements, please run the below select statement to make sure data is inserted in a proper way as shown below.

SELECT * FROM SAP_STUDENT.ORDERS_DATA_RANK;

1

RANK() using SQL Script:

  • Based on our scenario, we have to partition our data set by ORDER_NO and then arrange it in descending order based on ORDERED_TIME column to get top (first) rank to the recently changed sales orders.
  • We can achieve this by writing the below SQL statement, which calculates the RANK based on PARTITION and ORDER BY clause and assigns the ranks as 1,2,3 and so on.

2

  • Once we arrange data set as shown above with the help of RANK function, we can just filter the RANK value to ‘1’ to get recent records to the output.

RANK() using Graphical Calculation View:

  • Now let’s see how we can achieve same thing using rank node available while creating graphical calculation view.
  • Note: This node is available in graphical calculation view only from SAP HANA SP9 revision.

Please follow the below steps to build rank functionality using calculation view.

Step1:

  • Make sure we have the above table created and data present in SAP HANA system.
  • Note: Individuals can also use their own data set to check the functionality.

Step2:

  • Let’s go ahead and create a new calculation view of type ‘graphical’ and specify details like technical name and label as shown below.

Please go though ‘Graphical Calculation View in SAP HANA for complete information on calculation view.

3

Step3:

  • We can see the ‘Rank’ node available on the left hand side of the screen. Click on that node and click again on design area to add rank node to our design area as shown below.

4

Proceed to the next page to continue reading…

By | 2016-03-22T04:15:52+00:00 September 11th, 2015|HANA|12 Comments

12 Comments

  1. Curso SAP BO September 6, 2016 at 2:08 AM - Reply

    Exist a version problem with ranking node? because en build 85v not work.

    • sap student September 6, 2016 at 6:51 AM - Reply

      Yes, it only works from SAP HANA SPS09 (version 90 onwards).

      • Curso SAP BO September 6, 2016 at 7:05 PM - Reply

        Thanks, i will talk with a basis team. Best Regards!

  2. Yamamoto September 16, 2016 at 10:05 PM - Reply

    Hello,

    Im with SP09 rev97 and theres no Generate Rank Column option.
    Which rev is phis option available?

    Thx in advance.

    • sap student September 16, 2016 at 10:33 PM - Reply

      Hi,

      This option is supported from SAP HANA SPS10.

      • Yamamoto September 19, 2016 at 6:06 PM - Reply

        Thank you for the answer

  3. test123 September 23, 2016 at 12:34 PM - Reply

    Hi,

    I create a SQL view with this query statement:
    “SELECT
    COUNT(colA) counts,
    MIN(created_at) created_at,
    MAX(order_no) max_no,
    MAX(last_updated_at) last_updated,
    colA,
    colB,
    (case when colC = 0 then ‘NO’ else ‘YES’ end) colC,
    FROM (
    SELECT
    colA,
    colB,
    colC,
    created_at,
    last_updated_at,
    ROW_NUMBER() OVER(PARTITION BY colA, colB, colC) AS order_no
    FROM table1
    )
    GROUP BY colA, colB, colC ”

    My problem is: how can I create a column view (Attribute, Analytic, Calculation View) based on this query?
    Please teach me how to do that.

    Thanks a lot

  4. bishant December 30, 2016 at 7:55 PM - Reply

    Hi,

    I need to use distinct value for one column. Can I use Rank function to achieve. If yes, please let me know how.

    Thanks

    • sap student December 31, 2016 at 1:14 AM - Reply

      Hi Bishant,

      To calculate distinct count you don’t need rank function, you can do this using ‘Counter’ in either Calculation View (Cube type) or Analytic View.

  5. bishant January 1, 2017 at 8:40 PM - Reply

    Hi,

    My query is when we use select distinct statement for any more than three column. How can we achieve this graphically. By using counter we can get the distinct count but I need the distinct column value. I hope it makes somewhat clear my query.

    Thanks

    • sap student January 4, 2017 at 4:22 AM - Reply

      Hi Bishant,

      Use ‘Aggregate Node’ and add the columns for which you need distinct values as Attributes to the output. Don’t add any measures into it. The output of the aggregate node should be same as SELECT DISTINCT COL1,COL2,COL3… FROM TABLE statement.

  6. Lakshminarasimhan May 1, 2017 at 1:32 PM - Reply

    excellent article. Thanks for sharing

Leave A Comment

twelve + sixteen =

Skip to toolbar