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,
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
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.
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.
We can get the final result by Joining Time Series with the Ranges to get QTY for each date.
CODE: -
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;- SAP Managed Tags:
3 Comments
