Lookup formula in Calculation function

helga03 — Tue, 02 Jun 2026 06:51:39 GMT

Environment: SAP Profitability and Performance Management 3.0 SP22, On-Premise.

Lookup formula in Calculation function: right-hand join key always quoted as string literal in generated HANA code

Hello,

I am trying to enrich my input data in a Calculation function by pulling fields from another Model Table via a Lookup formula, following the approach from the sample content and older community posts.

Setup:

Calculation type: Relative
Input function: Model Table 13852 (main stream)
Lookup tab: Model Table 13854 added
In the Action of my first rule, I want to fill the field COUNTERPARTY_TYPE from table 13854, joined on COUNTERPARTY_ID.

Formula I entered:

13854.COUNTERPARTY_TYPE[COUNTERPARTY_ID=COUNTERPARTY_ID]

Problem: In the generated HANA code, the right-hand side of the join condition is always wrapped in single quotes, so it is treated as a string literal instead of a field reference from the main stream:

sql

IFNULL((SELECT MAX(COUNTERPARTY_TYPE) FROM "SAPDB"."Y820HZL000513854"
        WHERE (COUNTERPARTY_ID = 'COUNTERPARTY_ID')), TO_NVARCHAR('')) AS COUNTERPARTY_TYPE

Because of this, the lookup never matches any row and always returns the empty default. The enriched field stays blank, and all my downstream rules that depend on it do not trigger.

What I already tried (each one re-activated and re-checked in the generated code):

Formula Generated WHERE clause

`...[COUNTERPARTY_ID=M.COUNTERPARTY_ID]`	`WHERE (COUNTERPARTY_ID = 'M.COUNTERPARTY_ID')`
`...[COUNTERPARTY_ID=COUNTERPARTY_ID]`	`WHERE (COUNTERPARTY_ID = 'COUNTERPARTY_ID')`
`...[COUNTERPARTY_ID=$COUNTERPARTY_ID]`	`WHERE (COUNTERPARTY_ID = '$COUNTERPARTY_ID')`

In every case the right-hand value is quoted as a literal string. The main stream is aliased as AS M in the generated code, so I expected M.COUNTERPARTY_ID to work as a field reference, but it is quoted too.

My questions:

How do I enter the main-stream field on the right-hand side of the lookup condition so that it is interpreted as a field reference and not as a string literal?
Is there a dedicated field picker in the formula editor for this, instead of typing the field name as text?
Is the bracket lookup syntax FID.FIELD[KEY=KEY] still supported in 3.0 SP22, or has the recommended approach changed (e.g. using the Lookup tab join configuration instead of a formula)

Any guidance on the correct syntax or configuration would be greatly appreciated. Thank you!

Re: Lookup formula in Calculation function

Leonardo-Silva — Tue, 02 Jun 2026 19:56:14 GMT

Hello @helga03

In PaPM Cloud, the way you did it would work. For OnPrem, I remembered this blog post where the reply suggested using parentheses to avoid parsing as a string. Try this one:

13854.COUNTERPARTY_TYPE[(COUNTERPARTY_ID=M.COUNTERPARTY_ID)]

Regards

Leo

Question Re: Lookup formula in Calculation function in Technology Q&A

Lookup formula in Calculation function

Re: Lookup formula in Calculation function