Skip to Content
0

SAP HANA Calculated column if statement

Dec 14, 2016 at 04:34 PM

901

avatar image

Hi Expert,

I am creating a calculated column in SAP HANA using an if statement but i am encountering some challenges.

The idea of the if statement is that i have three columns; trsfrsum,cashsum and checksum.

If the checksum !=0 then it should display the checksum amount, if the trsfrsum !=0 then display transfer amount and if cashsum !=0 then display cashsum. Which will all be saved in a calculated column called "Amount".

i did this but it is not working as expected. Any solution to this will be appreciated.

Attached is the image of what i wrote.

Regards

Justice

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Florian Pfeffer
Dec 14, 2016 at 04:47 PM
0

You need several if function calls for that. With one it is not possible in the way you trying it. Also consider that the isNull function is not the same than "!=0" as described by you.

The following expression fits your text description:

if("CheckSum" != 0,"CheckSum", if("TrsfrSum" != 0,"TrsfrSum", if("CashSum" != 0,"CashSum",0)))

Regards,
Florian

Show 9 Share
10 |10000 characters needed characters left characters exceeded

Hi Florian,

What about, the following conditions are required? If trsfrsum !=0 then TrsfrAcct, if Checksum !=0 then checkAcct and if cashsum !=0 then CashAcct.

I tried your above code and i got the attached error. Please take a look .

Regards

Justice

0

What types your columns have?

0

Types for TrsfrAcct and other Acct are Varchar

0

So "varchar" should give you the hint, why the coding fails. Comparing a varchar value against a number value produces the error message.

In case your "varchar" columns contains only number values you could cast the varchar values to e.g. doubles like following:

if(double("CheckSum") != 0,double("CheckSum"), if(double("TrsfrSum") != 0,double("TrsfrSum"), if(double("CashSum") != 0,double("CashSum"),0)))

But from my point of view you have already a not clean design of your data model when you store number values (which are indicated by the column names e.g. "..sum..") in varchar columns.

0

I understand you, but the aim of my calculated column with reference to Acct is to be able to join my calculated column that will hold GL account codes of different payment means to the GL Account view that has the Acct code and AcctName.

So I am trying to display transfer account code when the transfer amount is not Zero and some for the other accounts.

That's the aim of my calculated column

0

With the provided information you should be able to solve your requirement easily.

0
Show more comments