LOOKUP

Referring to Another Source Value Using the LOOKUP Function

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

Syntax

*LOOKUP {App}

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

*DIM MEASURES=”MeasureName”

*ENDLOOKUP

Example Example

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

AppSet: APSHELL_V (Copy of APSHELL)

Application: 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

End of the example.

Example 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 cube. Then, you use them in the calculation.

Values in the cubes before the execution of the script:

PLANNING Cube data:

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

Rate Cube 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 cube 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.

End of the example.

Example 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

End of the example.

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.