LOOKUP

A function that refers to another source value

To calculate a new value inside a *REC( ) statement, you can use the LOOKUP function to assign a source value that is outside the scoped data for a FACTOR or an EXPRESSION instruction.

Syntax

*LOOKUP {Model}

*DIM [{LookupID}:]{DimensionName}="Value" | {CallingDimensionName}[.{Property}]

*DIM MEASURES="MeasureName"

*ENDLOOKUP

Example

In the following example, you are reading RATE information and using it in the calculation. You can also use FOR/NEXT to look up multiple values.

Environment: EnvironmentShell_V (Copy of the EnvironmentShell sample environment)

Model: Planning

*XDIM_MEMBERSET TIME = 2006.AUG

*XDIM_MEMBERSET P_ACCT=CE0004010

*XDIM_MEMBERSET CATEGORY=ACTUAL

*LOOKUP RATE

*DIM TIME="2006.AUG"

*DIM CATEGORY="ACTUAL"

*DIM R_ACCT="AVG"

*DIM R_ENTITY="GLOBAL"

*DIM RATEEUR:INPUTCURRENCY="EUR"

*DIM MEASURES="PERIODIC"

*ENDLOOKUP

*WHEN P_ACCT

*IS "CE0004010"

*REC(EXPRESSION=%VALUE%/LOOKUP(RATEEUR), RPTCURRENCY="EUR")

*ENDWHEN

Example

In the following example, you are first reading all reporting currencies in the *SELECT statement and assigning the values to variable %CUR%. Using FOR/NEXT, you read their rates from RATE model. Then, you use them in the calculation.

Values in the models before the execution of the script:

PLANNING model data:

TIME	P_ACCT	ENTITY	P_ACTIVITY	CATEGORY	Currency	P_DATASRC	SignData
2006.AUG	CE0004010	C9000	NONE	ACTUAL	LC	UPLOAD	157,915.81

Rate model data:

Category	InputCurrency	R_ACCT	R_ENTITY	TIME	SignData
ACTUAL	EUR	AVG	GLOBAL	2006.AUG	1.5022
ACTUAL	USD	AVG	GLOBAL	2006.SEP	1.0000

*XDIM_MEMBERSET TIME=2006.AUG

*XDIM_MEMBERSET P_ACCT=CE0004010

*XDIM_MEMBERSET CATEGORY=ACTUAL

*SELECT(%CUR%, "[ID]", RPTCURRENCY, "[REPORTING]=Y")

*LOOKUP RATE

*DIM CATEGORY="ACTUAL"

*DIM R_ACCT="AVG"

*DIM R_ENTITY="GLOBAL"

*DIM TIME="2006.AUG"

*DIM MEASURES="PERIODIC"

*FOR %LOOP_CUR%=%CUR%

*DIM C_%LOOP_CUR%:INPUTCURRENCY="%LOOP_CUR%"

*NEXT

*ENDLOOKUP

*WHEN P_ACCT

*IS "CE0004010"

*FOR %LOOP_CUR%=%CUR%

*REC(EXPRESSION=%VALUE%/LOOKUP(C_%LOOP_CUR%), RPTCURRENCY=%LOOP_CUR%)

*NEXT

*ENDWHEN

Values in the model after the execution of the script:

2006.AUG	CE0004010	C9000	NONE	ACTUAL	EUR	UPLOAD	105,123.03
2006.SEP	CE0004010	C9000	NONE	ACTUAL	USD	UPLOAD	157,915.81

You can also pass property values to scope members to look up data.

Example

In this example, the corresponding Entity's currency is read from master data and this is used to look up the rate value.

*XDIM_MEMBERSET TIME = 2007.AUG

*XDIM_MEMBERSET P_ACCT=CE0004010

*XDIM_MEMBERSET CATEGORY=ACTUAL

*XDIM_MEMBERSET ENTITY=C9000

*LOOKUP RATE *DIM TIME="2006.AUG"

*DIM CATEGORY="ACTUAL"

*DIM R_ACCT="AVG"

*DIM R_ENTITY="GLOBAL"

*DIM RATE:INPUTCURRENCY=ENTITY.CURRENCY

*DIM MEASURES="PERIODIC"

*ENDLOOKUP

*WHEN P_ACCT

*IS "CE0004010"

*REC(EXPRESSION=%VALUE%/LOOKUP(RATE), RPTCURRENCY=ENTITY.CURRENCY)

*ENDWHEN

Restriction

Reading multiple measures within a single LOOKUP by assigning different lookup IDs is not supported.

Any software coding and/or code lines / strings ("Code") included in this documentation are only examples and are not intended to be used in a productive system environment. The Code is only intended to better explain and visualize the syntax and phrasing rules of certain coding. SAP does not warrant the correctness and completeness of the Code given herein, and SAP shall not be liable for errors or damages caused by the usage of the Code, except if such damages were caused by SAP intentionally or by its gross negligence.