on 04-19-2020 3:09 PM
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 !
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.