In this blog post I am going tp write about HANA Lead and Lag Functions. The functionality is same as SQL but I found it little weird while implementing this one 1 of the HANA Graphical calculation Views.

Doing in scripted CVs it is pretty easy but while doing in Graphical HANA CVs these functions are were not available on HANA graphical CV. it can be done in HANA Graphical CV also I am not denying that but here I am going to focus on the understanding with a clear example.

Later once we understand the concept please read the latest blog post on availability of Window Function Node on Graphical CV modelling on SAP Business Application Studio.

First let us understand what is Lead and Lag functions then read the blog post already written by one the bloggers about the window function nodes available on SAP Business Application Studio on HANA 2.0 SPS 05.

Please see the example here : –

Syntax of Lead function

LEAD(scalar_expression ,offset [,default]) OVER (  [PARTITION BY partition_expression, … ]

ORDER BY sort_expression [ASC | DESC], …

Syntax of Lag function

LAG (scalar_expression [,offset] [,default])  OVER ( [ partition_by_clause ] order_by_clause

• Scalar_expression: It contains the column name or expression for which we want to apply the lead function.

• Offset: It is the number of rows ahead from the current row. The lead function uses this argument to fetch the value. We can use an expression, subquery or a positive integer value in this argument. It is an optional argument. If we do not specify any value, SQL Server considers the default value as positive integer value 1

• Default: We can specify a default value for this argument. If the SQL Server Lead function crosses the boundary of a partition of no values are available, it shows the default value in the output. It is also an optional parameter and returns NULL if no values are specified.

• PARTITION BY: We can create data set partitions using PARTITION BY argument. It is also an optional argument. If we do not specify this argument, SQL Server considers complete data set a single partition.

• ORDER BY: Order by clause sorts the data in ascending or descending order. If we use partitions ( by PARTITION BY clause), it sorts the data in each partition.

Example :

CREATE TABLE Data_SQL (class CHAR(10), val INT, offset INT);
 INSERT INTO T VALUES('A', 1, 1);
 INSERT INTO T VALUES('A', 3, 3);
 INSERT INTO T VALUES('A', 5, null);
 INSERT INTO T VALUES('A', 5, 2);
 INSERT INTO T VALUES('A', 10, 0);
 INSERT INTO T VALUES('B', 1, 3);
 INSERT INTO T VALUES('B', 1, 1);
 INSERT INTO T VALUES('B', 7, 1);

SELECT class, 
  val, 
  offset,
  LEAD(val) OVER (PARTITION BY class ORDER BY val) AS lead,
  LEAD(val, offset, -val) OVER (PARTITION BY class ORDER BY val) AS lead2,
  LAG(val) OVER (PARTITION BY class ORDER BY val) AS lag,
  LAG(val, offset, -val) OVER (PARTITION BY class ORDER BY val) AS lag2
 FROM T;

Lead Lad Explain in a single picture with easy explanation : Remember the partition by Columns and then it is easy for you.

Now I do not want to write the same blog which is already written so I suggest to watch this blog for detailed information.

In the past, they were generally available only in sql scripting objects like Table functions and Stored procedures but not available to use graphically in a CV.

https://blogs.sap.com/2022/03/26/window-functions-availability-in-graphical-cv-modeling-bas/

Thank You.

Narasingha Patro.