HOW TO GENERATE ROW NUMBER OR SEQUENCE NUMBER USING HANA GRAPHICAL CALC VIEW RANK
HOW TO GENERATE ROW NUMBER OR SEQUENCE NUMBER USING SAP HANA GRAPHICAL CALC VIEW RANK
By: Muhammad Mubashar Iqbal
PURPONSE OF THIS DOCUMENT:
To show how to use RANK to generate the row numbers in SAP HANA Graphical Calculation view. I was unable to find any function or expression to generate row number in HANA Graphical Calculation view, so I used RANK to achieve my requirement. I don't have to use
SQL Script calc view to generate the row number.
Created a Table name Country in SAP HANA and have following columns:
COUNTRY_NAME VARCHAR (50)
COUNTRY_ID INTEGER ( This column is not showing country ID's but using it to generate rows with 1 as value)
Inserted values in Country Table and it has 5 countries
Creating Calculation view in SAP HANA using graphical type
Added country table in projection
Click ok, country table is added & select columns from Projection
Click On RANK & drop
CONNECT COUNTRY PROJECTION TO RANK and double click on Rank
Select COUNTRY & COUNTRY_ID and screen will look like below
Click on Rank and please do the following steps:
Click on Threshold & set your threshold limit
ORDER BY : SELECT COUNTRY_ID in order By
NOTE: YOU CAN DEFINE CALCULATED COLUMN AS AN INTEGER OR DOUBLE AND SET THE VALUE 1
AND YOU CAN ALSO USE THIS CALCULATED COLUMN INSTEAD OF USING TABLE COLUMN.
I AM USING COUNTRY_ID FROM TABLE TO DEMONSTRATE THAT WE CAN ACHIEVE ROW_NUM GENERATION OR SEQ_NUM GENERATION USING GRAPHICAL CALC VIEW.
Partition By: Add COUNTRY_ID in partition by Column
Click on Generate Rank Column: Add column title you need like ROW_NUM etc.
CONNECT RANK projection to final projection:
Click on Activate & Preview. I have clicked ROW_NUM to sort in Ascending