Skip to Content
Technical Articles
Author's profile photo Naim Khan

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

Hello everyone,

REQUIREMENT

To access previous row value in a table. When we have null value in the current row then we have to look backward for the previous rows till we find a value .

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

Before proceeding further I would like to thank @konrad for his suggestion to improvise this blog post.

Scenario 2: – To access previous row value

DATA

We will use the same table in this case also as used in Scenario 1, 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 backward/previous months whenever there is a null value for QTY, then for Feb and March, we should have values from Jan (which is the first non-null value month before Feb and March). Similarly, for May, Aug, Sept and Nov we have to look previous months for value.

In simple words,

  • Feb and March will have values from Jan.
  • May will have value from April.
  • Aug and Sept will have values from July.
  • Nov will have value from Oct.

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

 

SOLUTION

Scenario 2: – To access previous row value

DETERMINE FROM_DATE and TO_DATE RANGES FOR GIVEN NON-EMPTY QTY

As you can see the output, value from Jan should be replicated to Feb and Mar, so FROM_DATE is Jan and TO_DATE is Mar, similarly, you can see values of other months too.

SELECT "PRODUCT", 
       "LOCATION", 
       "QTY", 
       "DATE" AS "FROM_DATE",
       IFNULL( ADD_MONTHS( LEAD("DATE") OVER(PARTITION BY "PRODUCT", "LOCATION" ORDER BY  "DATE") , -1), "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",
	 "DATE" AS "FROM_DATE" ,
	 IFNULL( ADD_MONTHS( LEAD("DATE") OVER(PARTITION BY "PRODUCT","LOCATION" ORDER BY "DATE") , -1), "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_BACKWARD" ( ) 
	
      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",
		 "DATE" AS "FROM_DATE" ,
		 IFNULL( ADD_MONTHS( LEAD("DATE") OVER(PARTITION BY "PRODUCT","LOCATION" ORDER BY "DATE") , -1), "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;

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Konrad Zaleski
      Konrad Zaleski

      Hi Naim,

      Very nice article!

      I have a comment regarding your example - In the real life, usually you need to support a wider date range, which in addition dynamically changes.

      To make the query more dynamic, I would suggest:

      1. Determine FROM_DATE and TO_DATE ranges for given non-empty Qty

      			SELECT "PRODUCT", "LOCATION", "QTY", "DATE" AS "FROM_DATE"
      				, IFNULL( ADD_MONTHS( LEAD("DATE") OVER(PARTITION BY "PRODUCT", "LOCATION" ORDER BY  "DATE") , -1), "DATE") AS "TO_DATE"
      			FROM "SCHEMA"."BASE_TABLE"
      			WHERE "QTY" IS NOT NULL
      			ORDER BY "DATE"

      2. Use GENERATE_SERIES (or M_TIME_DIMENSION table) to generate Time Series (monthly intervals)

      	SELECT 
          	"GENERATED_PERIOD_START" AS "DATE"
          FROM 
          	SERIES_GENERATE_DATE('INTERVAL 1 MONTH', '2010-01-01', '2050-01-01') TIME_SERIES

      3. Join Time Series with the Ranges to get QTY for each date to get the final results:

      	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", "DATE" AS "FROM_DATE"
      				, IFNULL( ADD_MONTHS( LEAD("DATE") OVER(PARTITION BY "PRODUCT", "LOCATION" ORDER BY  "DATE") , -1), "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"
      		;	

      Final results will be the same as yours, however it will reflect also new dates that will appear in the underlying table.

      Regards,

      Konrad

      Author's profile photo Naim Khan
      Naim Khan
      Blog Post Author

      Hi Konrad,

      Thanks for the comment and for sharing this information.
      I have edited the blog as per your suggestion.
      Once again thanks for the kind words.

      Best Regards,

      Naim Khan

      Author's profile photo Lars Breddemann
      Lars Breddemann

      Konrad's solution works very nicely and is likely more efficient than the extensive SQLScript approach.

      The problem itself (filling "gaps" in data with the last non-NULL value based on some notion what "last" means) is not new at all.

      This post on askTom from 2008 (!) https://asktom.oracle.com/pls/apex/asktom.search?tag=recursive-lag-function demonstrates

      1. that HANA lacks some nice features when it comes to window aggregation functions (IGNORE NULLS!)
      2. that a solution without joins and generating an auxiliary calendar data set is relatively easy to achieve:
       SELECT product, location
           , to_integer (substring (max(qty) OVER (PARTITION BY product, location 
                                             ORDER BY date asc)
                                  , 12))  theqty
       from
           (SELECT product, location
              , CASE WHEN qty IS NOT NULL THEN
                  LPAD(ROW_NUMBER() OVER (PARTITION BY product, location 
                                          ORDER BY "DATE" ASC)
                      , 10, '0')|| '|' ||qty 
                END "QTY"
              , "DATE"
            FROM prod_loc)
       ORDER BY 
          product, location, "DATE" ASC ;