Repeat data for missing months in HANA calculation view

[sandeepbose]

I need to adjust my calculation view to obtain below results

Repeat previous month record for current month, If its missing for current month.

Example- Table data i have

Month Material Plant Value Qty Price

201901 M001 P001 500 10 50

201904 M001 P001 700 15 47

201910 M001 P001 900 12 75

Output I need -

Month Material Plant Value Qty Price

201901 M001 P001 500 10 50

201902 M001 P001 500 10 50

201903 M001 P001 500 10 50

201904 M001 P001 700 15 47

201905 M001 P001 700 15 47

201906 M001 P001 700 15 47

201907 M001 P001 700 15 47

201908 M001 P001 700 15 47

201909 M001 P001 700 15 47

201910 M001 P001 900 12 75

201911 M001 P001 900 12 75

201912 M001 P001 900 12 75

How can this be achieved using HANA CV ?

Thanks for your help !

[Abhishek_Hazra]

Hi bosesandeep1,

You can implement such a solution with help of table functions in HANA. You can simply try out the drafted script mentioned later below in this answer in a table function & consume it in your graphical calculation view. I have created the table : DATA_COPY as per the data provided by you in question. The raw data looks like below :

As next step, I have made a left outer join with M_TIME_DIMENSION_MONTH table (left table in join) in _SYS_BI schema in order to generate the intermediate months with null values for the relevant columns to look for in previous months.

Now you can assign the relevant values as required by using below script in a table function with help of rank over partition by function :

do
begin

DECLARE i int = 0;
DECLARE tt_interim table (
"CALMONTH" NVARCHAR(6),
"MONTH" NVARCHAR(6),
"MATERIAL" NVARCHAR(4),
"PLANT" NVARCHAR(4),
"VALUE" DEC(17,2),
"QUANTITY" DEC(17,3),
"PRICE" DEC(17,2));

tt_month = select  rank() OVER (ORDER BY b."CALMONTH") as "rank", 
b."CALMONTH",
a."MONTH",
"MATERIAL",
"PLANT",
"VALUE",
"QUANTITY",
"PRICE" from _SYS_BI."M_TIME_DIMENSION_MONTH" as b LEFT OUTER JOIN "DATA_COPY" as a 
ON b."CALMONTH" = a."MONTH"
WHERE b."YEAR" = '2019';

while :i < 50 do  
 
insert into :tt_interim (select 
t2."CALMONTH",
t1."MONTH",
t1."MATERIAL",
t1."PLANT",
t1."VALUE",
t1."QUANTITY",
t1."PRICE" 
from :tt_month as t1 INNER JOIN :tt_month as t2 
ON 1 = 1
WHERE t1."rank" = t2."rank" - i AND t1."MONTH" IS NOT NULL);

i := i+1;
end while; 

tt_final = select * from (
select rank() over (partition by "CALMONTH" order by "MONTH" desc) as "rank", *
from :tt_interim ) where "rank" = 1 ;

select 
"CALMONTH",
"MATERIAL",
"PLANT",
"VALUE",
"QUANTITY",
"PRICE"
from :tt_final ORDER BY "CALMONTH";

end;

This script gets back with the data you requested for in the question.

Note : The example is created by the data provided by you, please adjust the script based on your actual business case eg. proper key check within where clause, removal of year filter on '2019' etc. 🙂

If this apprears helpful to your need, an acceptance of the answer will really be appreciated. 😉

Cheers!
Abhi