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

score 1 · Answer 1 · answered Oct 18 '18 at 12:27

Simplest SQL Select for this statement will be as follows including a self-join without using sql windows functions

select 
    Table1.*,
    case 
        when Table1.KSCHL = 'Y' 
        then Table1.KBETR 
        else
            Table1.KBETR * t2.KBETR
    end as Expected
from Table1
left join Table1 as t2
    on Table1.matnr = t2.matnr and
       Table1.werk  = t2.werk and
       t2.kschl = 'Y';

A possible problem with your question is; what will happen if there are two Y condition type. I assume you ensure that there will be only one value at a time.

score 1 · Accepted Answer · answered Oct 18 '18 at 12:36

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,

HANA - Calculate using data from other line

2 Answers2