HANA - Calculate using data from other line

Question

I have a requirement, use attribute view to calculate the result base on condition and value come from another line.

If KSCHL = Y then Result will be set from KBETR. If not, Result will be set by it own KBETR * KBETR from corresponding key pair (MATNR, WERKS). Please see the example.

MATNR	WERK	KSCHL	KBETR	Expected Result
01	A	X	10	200 x 10
01	A	Y	200	200
01	A	Z	15	200 x 15

I thinking about add a hidden column to store the value 200 on other lines then use it to do the multiple, or create a calculation view with parameter to hold the value. However I still not get the right answer for the requirement.

Thanks for your help, very appreciated

Binh

Answer 1

Another option is using SQL First_Value() analytic function.

You can refer to given example, though the sample is on SQL Server the function has the same usage and syntax on HANA database for SQLScript developer

Here is the sample SQL query for your HANA database

select  
    *,
    case 
        when KSCHL = 'Y' 
        then KBETR 
        else
            KBETR * ( first_value(KBETR) over (partition by matnr, werk order by case when kschl = 'Y' then 0 else 1 end) )
    end as Expected
from Table1

The first_value() function fetches the KBETR value ordered by CASE statement (to get Y conditions at the beginning for First_Value function) for each MATNR, WERK combination defined with Partition By clause

I hope it helps,