How to get Next Row value in a table in SAP HANA/HANA SQL

[naimkhan786]

Hello everyone,

REQUIREMENT

To access next row values in a table. When we have null value in the current row then we have to look forward for the upcoming rows till we find a value .

In this blog post we will discuss how to get next row value in a table and to explore further on how to access previous row value please have a look at my next BLOG POST (Scenario 2).

When you have two values in a single month for example a product having two version 001 and 002 then kindly have a look at my Blog Post.

Scenario 1: - To access next row value

DATA

Let me first introduce you with the reference table which has four columns namely PRODUCT, LOCATION, QTY (QUANTITY), DATE.

This table contains the list of ordered quantity of a product (i.e. material) from a location (i.e. plant) for an entire year. If you look at the table, In Jan, we have ordered 10 quantity and there is null quantity ordered in Feb. Similarly, we have values of ordered quantity for the rest of the months.

Now, when I say we have to look forward whenever there is a null value for QTY, then for Feb and March, we should have values from April (which is the first non-null value month after Feb and March). Similarly, for May, Aug, Sept and Nov we have to look forward months for value.

In simple words,

• Feb and March will have values from April.


• May will have value from June.


• Aug and Sept will have values from Oct.


• Nov will have value from Dec.

To achieve this, I have used Table Function, which can be further consumed in a Calculation view to get the result.

 

SOLUTION

Scenario 1: - To access next row value

DETERMINE FROM_DATE and TO_DATE RANGES FOR GIVEN NON-EMPTY QTY

As you can see the output, value in Feb and March should be replicated from April, so FROM_DATE is Feb and TO_DATE is Apr,

May will have value from June, hence FROM_DATE is May and TO_DATE is June,

similarly, you can see values of other months too.

SELECT "PRODUCT",

       "LOCATION",

       "QTY",		 

       IFNULL( ADD_MONTHS( LAG("DATE") OVER(PARTITION BY "PRODUCT","LOCATION" ORDER BY "DATE") , 1), "DATE") AS "FROM_DATE",

       "DATE" AS "TO_DATE" 

	FROM "SCHEMA"."BASE_TABLE" 

	WHERE "QTY" IS NOT NULL 

	ORDER BY "DATE";

USE GENERATE_SERIES (or M_TIME_DIMENSION table) TO GENERATE TIME SERIES (monthly intervals)

Here, we will use generate series to generate a year to cover from Jan to Dec, to support a wider date range you can simply change the value of Dates passed in the function.

SELECT 

       "GENERATED_PERIOD_START" AS "DATE"

    FROM 

    	SERIES_GENERATE_DATE('INTERVAL 1 MONTH', '2021-01-01', '2022-01-01') TIME_SERIES;

FINAL RESULT

We can get the final result by Joining Time Series with the Ranges to get QTY for each date.

SELECT

	 "PRODUCT",

	 "LOCATION",

	 "QTY" ,

	 "GENERATED_PERIOD_START" AS "DATE" 

FROM SERIES_GENERATE_DATE('INTERVAL 1 MONTH','2010-01-01','2050-01-01') TIME_SERIES JOIN ( 

SELECT

	 "PRODUCT",

	 "LOCATION",

	 "QTY",

	 IFNULL( ADD_MONTHS( LAG("DATE") OVER(PARTITION BY "PRODUCT","LOCATION" ORDER BY "DATE") , 1), "DATE") AS "FROM_DATE",

	 "DATE" AS "TO_DATE" 

	FROM "SCHEMA"."BASE_TABLE" 

	WHERE "QTY" IS NOT NULL 

	ORDER BY "DATE" ) TAB ON TIME_SERIES."GENERATED_PERIOD_START" BETWEEN TAB."FROM_DATE" AND TAB."TO_DATE" 

;

CODE: -

FUNCTION "XXXX"."YYYYY::TF_LOOKING_FORWARD" ( ) 

	

      RETURNS TABLE (

	               "PRODUCT" NVARCHAR(18),

	               "LOCATION" NVARCHAR(4),	               

	               "QTY" DECIMAL(15,6), 	               

	               "DATE" NVARCHAR(10)

	               )

	

	LANGUAGE SQLSCRIPT

	SQL SECURITY INVOKER AS

BEGIN



/***************************** 

Write your function logic

 *****************************/



var_out = SELECT

	 "PRODUCT",

	 "LOCATION",

	 "QTY" ,

	 "GENERATED_PERIOD_START" AS "DATE" 

FROM SERIES_GENERATE_DATE('INTERVAL 1 MONTH','2010-01-01','2050-01-01') TIME_SERIES JOIN ( 

SELECT

	 "PRODUCT",

	 "LOCATION",

	 "QTY",

	 IFNULL( ADD_MONTHS( LAG("DATE") OVER(PARTITION BY "PRODUCT","LOCATION" ORDER BY "DATE") , 1), "DATE") AS "FROM_DATE",

	 "DATE" AS "TO_DATE" 

	FROM "SCHEMA"."BASE_TABLE" 

	WHERE "QTY" IS NOT NULL 

	ORDER BY "DATE" ) TAB ON TIME_SERIES."GENERATED_PERIOD_START" BETWEEN TAB."FROM_DATE" AND TAB."TO_DATE" 

;	

return :var_out;



END;