SAP HANA Calculated column if statement

[millicentdark]

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

[pfefferf]

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

[millicentdark]

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

[pfefferf]

What types your columns have?

[millicentdark]

Types for TrsfrAcct and other Acct are Varchar

[pfefferf]

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.

[millicentdark]

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

[pfefferf]

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

[millicentdark]

Florian,

That is what I believe I did but got the error shared above.

[pfefferf]

Then apply the casting please. It is not so hard.

[millicentdark]

Florian ,

i did this for the numeric to nvarchar but i believe i missed something.

if(cast("CheckSum" as Nvarchar) != 0,cast("CheckSum" as Nvarchar), if(cast("TrsfrSum" as Nvarchar) != 0,cast("TrsfrSum" as Nvarchar), if(cast("CashSum" as Nvarchar) != 0,cast("CashSum" as Nvarchar),0)))