Lookup in a Calculation Function

[philippsathasivam]

Hello,

i saw in a sample Content that it is possible to combine two Tables with a Lookup and make Calculations, so you dont have to use a Join. So i tried to combine two Tables, Table 1 and Table 2, please see below:

I used Table 1 as Input in my Calculation Function:

As lookup-Input i used Table 2:

Then in the Formula of the Field AMOUNT1 I typed in the following Logic: The Lookup Field is GL_ACCOUNT

I also typed in the following different Logics, which was also possible to activate:

So it should add everywhere where GL_ACCOUNT matches (ACC1 and ACC2) a 10 if its ACC1 and a 5 if its ACC2.

Its possible to activate this but when i see the results:

AMOUNT1 should be filled with 5+10; 4+10;3+10 for GL_ACCOUNT=ACC1 and 4+5;10+5 for GL_ACCOUNT=ACC2 right ?

No Addition takes place obviously. Can s.o tell me the correct Code please ?

Best Regards, Philipp

[zhiyitang · Accepted solution]

Hi philippsathasivam,

one of your formula was almost correct: AMOUNT1 + 14935.QUANTITY[GL_ACCOUNT=M.GL_ACCOUNT],

here M is the alias of the main/ master table variable.

But in order to let PaPM parse the variable M.GL_ACCOUNT correctly, you would need to use the parentheses to prevent this variable from being parsed as a string, so the right expression which can fulfill your need is:

AMOUNT1 + 14935.QUANTITY[(GL_ACCOUNT=M.GL_ACCOUNT)]

You may also use Function Attributes -> Runtime Attributes -> Stored Procedure to check if the generated HANA procedure correctly embodies your logic.

Best regards,

Zhiyi Tang

[philippsathasivam · Accepted solution]

Hi thanks for the Response, very cool !!!