CODEWITHSUNDEEP
if-statementexpressioncalculated-columnshana
croyx
croyx

Reputation: 19

sap hana calculated column check for multiple IDs

i want to create a calculated column, which will show two values: Y or N

2 columns are here important, "VAT-ID" and "CUSTOMER-ID". the calculated column will check if a customer-ID has multiple VAT-IDs. If yes the value "Y" should be displayed, else "N".

for example, the first 5 rows of the customer-id column are: 123456

654321

666666

123456

654321

the first 5 rows of the VAT-id column are: EE999999999

AA999999999

GG999999999

KK999999999

AA999999999

the first 5 rows of the calculated column should be then: Y

N

N

Y

N

any Help would be appreciated

Upvotes: 0

Views: 2231

Answers (2)

Eralper
Eralper

Reputation: 6612

As Lars mentioned it is not possible to use a window function within a calculated field on HANA table

But you can use following query to check if VAT number is multiple for a customer or not

select
CustomerId, VATID,
case 
    when (count(*) over (partition by CustomerId, VATID)) > 1 
    then 'Y' 
    else 'N' 
end
from CustomerVAT;

Upvotes: 0

Lars Br.
Lars Br.

Reputation: 10388

Calculated columns don’t allow for aggregations across groups or other than the current row.

What you can do to achieve your goal is to create a separate aggregation node and count distinct VAT-IDs grouped by CUSTOMER-ID.

With this, you can now have a calculated column that checks for VAT-ID-COUNT > 1 and map it to your Y/N values.

Upvotes: 1

Related Questions