cancel
Showing results for 
Search instead for 
Did you mean: 

*LOOKUP - using LOOKUP (somevalue) in *WHEN/*IS with *REC statement

maleodillet
Participant
0 Kudos

Hi Experts,

I am trying to use the *LOOKUP function to retrieve a value from a specified data intersection and evaluate this value using *WHEN/*IS to execute the *REC statement but it is not working. When I validate I get the following error

UJK_VALIDATION_EXCEPTION:Invalid dimension "LOOKUP(EX_FLAGMTD)" in model "SUMMARY"

Please kindly take a look at my code right below and advise. Thanks

*LOOKUP SUMMARY
*DIM Factor2:AUDIT_TRAIL = "INPUT_NON_RECURRING"
*DIM Factor2:COMMITMENT_ITEM = COMMITMENT_ITEM.ID
*DIM Factor2:CONTENT = %CONTENT_SET%
*DIM Factor2:COST_OBJECT = COST_OBJECT.ID
*DIM Factor2:CURRENCY = "LC"
*DIM Factor2:FUND = %FUND_SET%
*DIM Factor2:TIME = %TIME_SET%
*DIM Factor2:VERSION = %VERSION_SET%
*DIM Factor2:MEASURES = "PERIODIC"

*DIM EX_FLAGMTD:AUDIT_TRAIL = "EXCLUSION_FLAG_BASE"
*DIM EX_FLAGMTD:COMMITMENT_ITEM = COMMITMENT_ITEM.ID
*DIM EX_FLAGMTD:CONTENT = %CONTENT_SET%
*DIM EX_FLAGMTD:COST_OBJECT = COST_OBJECT.ID
*DIM EX_FLAGMTD:CURRENCY = "LC"
*DIM EX_FLAGMTD:FUND = %FUND_SET%
*DIM EX_FLAGMTD:TIME = %TIME_SET%
*DIM EX_FLAGMTD:VERSION = %VERSION_SET%
*DIM EX_FLAGMTD:MEASURES = "PERIODIC"

*ENDLOOKUP

*WHEN LOOKUP(EX_FLAGMTD)
*IS 1

*REC(EXPRESSION =[AUDIT_TRAIL].[CURRENCY_MIX] * LOOKUP(Factor2) , AUDIT_TRAIL = "INPUT_NON_RECURRING", VERSION = %VERSION_SET%,FUND = %FUND_SET%, CONTENT = %CONTENT_SET%)

*ENDWHEN 
*COMMIT
former_member186338
Active Contributor
0 Kudos

Sorry, but the question with both tags: BPC NW and BPC MS is meaningless. Please select only one!

Accepted Solutions (1)

Accepted Solutions (1)

former_member186338
Active Contributor
0 Kudos

The correct version using tuple expressions and ternary operator will be:

*XDIM_MEMBERSET COMMITMENT_ITEM AS %BSMEMBXCL%=BAS(FT10_CLASS.010,FT10_015_1.01,FT10_150_7)
*XDIM_MEMBERSET COMMITMENT_ITEM AS %BSMEMB%=BAS(%COMMITMENT_ITEM_SET%)
*XDIM_MEMBERSET COST_OBJECT AS %BSMEMBCO%=BAS(%COST_OBJECT_SET%) //??

*SELECT(%MEMBTTL%,ID,COMMITMENT_ITEM,[ID]=%BSMEMB% AND [ID]<>%BSMEMBXCL%)
*SELECT(%MEMBTTLCO%,ID,COST_OBJECT,[ID]=%BSMEMBCO%) //??
*SELECT(%RATE_VER%,[SOURCE],VERSION,ID=%VERSION_SET%) //%RATE_VER% - not used???

*XDIM_MEMBERSET AUDIT_TRAIL = CURRENCY_MIX
*XDIM_MEMBERSET CURRENCY = BAS(TCURR)
*XDIM_ADDMEMBERSET CURRENCY = USD, LC
*XDIM_MEMBERSET COMMITMENT_ITEM=%MEMBTTL% 
*XDIM_MEMBERSET CONTENT = %CONTENT_SET%
*XDIM_MEMBERSET COST_OBJECT = %MEMBTTLCO% //?? Why not simply: *XDIM_MEMBERSET COST_OBJECT = BAS(%COST_OBJECT_SET%)
*XDIM_MEMBERSET FUND = %FUND_SET%
*XDIM_MEMBERSET TIME = %TIME_SET%
*XDIM_MEMBERSET VERSION =%VERSION_SET%
*XDIM_MEMBERSET MEASURES = PERIODIC

*WHEN TIME
*IS * //everything is scoped
*REC(EXPRESSION =(([AUDIT_TRAIL].[EXCLUSION_FLAG_BASE],[CURRENCY].[LC])==1) ? %VALUE% * ([AUDIT_TRAIL].[INPUT_RECURRING],[CURRENCY].[LC]) : 0,AUDIT_TRAIL=INPUT_RECURRING)
*REC(EXPRESSION =(([AUDIT_TRAIL].[EXCLUSION_FLAG_BASE],[CURRENCY].[LC])==1) ? %VALUE% * ([AUDIT_TRAIL].[INPUT_NON_RECURRING],[CURRENCY].[LC]) : 0,AUDIT_TRAIL=INPUT_NON_RECURRING)
*ENDWHEN 

([AUDIT_TRAIL].[EXCLUSION_FLAG_BASE],[CURRENCY].[LC]) - this is the tuple expression!

former_member186338
Active Contributor

P.S. If you don't want to write 0 if ([AUDIT_TRAIL].[EXCLUSION_FLAG_BASE],[CURRENCY].[LC])<>1

Then write the same value to the target audittrail:

*REC(EXPRESSION =(([AUDIT_TRAIL].[EXCLUSION_FLAG_BASE],[CURRENCY].[LC])==1) ? %VALUE% * ([AUDIT_TRAIL].[INPUT_RECURRING],[CURRENCY].[LC]) : [AUDIT_TRAIL].[INPUT_RECURRING],AUDIT_TRAIL=INPUT_RECURRING)
maleodillet
Participant
0 Kudos

Hi Vadim,

As per my original question, your suggestion worked well, however, right now I am struggling with the second part of the Logic Script which involves reading a "Exclusion Flag" (Factor1) and multiplying the scooped members set by a "Distribution %" (Factor0) and we need to save it back to scooped data set.

this is my current workaround:

*XDIM_MEMBERSET COMMITMENT_ITEM AS %BSMEMBXCL%=BAS(FT10_CLASS.010,FT10_015_1.01,FT10_150_7)
*XDIM_MEMBERSET COMMITMENT_ITEM AS %BSMEMB%=BAS(%COMMITMENT_ITEM_SET%)
*XDIM_MEMBERSET CURRENCY AS %BSMEMBCURR%=BAS(TCURR)

*SELECT(%MEMBTTLCURR%,ID,CURRENCY,[ID]=%BSMEMBCURR% AND [ID]=USD)
*SELECT(%MEMBTTL%,ID,COMMITMENT_ITEM,[ID]=%BSMEMB% AND [ID]<>%BSMEMBXCL%)
*SELECT(%RATE_VER%,[SOURCE],VERSION,ID=%VERSION_SET%)
*SELECT(%MEMBENT%,ENTITY,COST_OBJECT,[ID]=%COST_OBJECT_SET%)

*XDIM_MEMBERSET AUDIT_TRAIL = INPUT_NON_RECURRING
*XDIM_MEMBERSET VERSION =%VERSION_SET%
*XDIM_MEMBERSET CURRENCY = %MEMBTTLCURR% 
*XDIM_MEMBERSET COMMITMENT_ITEM=%MEMBTTL%
*XDIM_MEMBERSET CONTENT = %CONTENT_SET%
*XDIM_MEMBERSET COST_OBJECT = BAS(%COST_OBJECT_SET%)
*XDIM_MEMBERSET FUND = %FUND_SET%
*XDIM_MEMBERSET TIME = %TIME_SET%
*XDIM_MEMBERSET MEASURES = PERIODIC

*FOR %SUFFIX% = _INP_E_

*LOOKUP SUMMARY

*DIM Factor0:AUDIT_TRAIL = CURRENCY_MIX
*DIM Factor0:VERSION = %RATE_VER%
*DIM Factor0:COMMITMENT_ITEM="CINR"
*DIM Factor0:CONTENT = "CONTINUING_SCE"
*DIM Factor0:COST_OBJECT = %SUFFIX%%MEMBENT%
*DIM Factor0:CURRENCY = CURRENCY.ID
*DIM Factor0:FUND=%FUND_SET%
*DIM Factor0:TIME=%TIME_SET%

*DIM Factor1:AUDIT_TRAIL = "EXCLUSION_FLAG"
*DIM Factor1:VERSION = %RATE_VER%
*DIM Factor1:CONTENT = "CONTINUING_SCE"
*DIM Factor1:CURRENCY = "LC"

*ENDLOOKUP

*WHEN TIME
*IS *

*REC(EXPRESSION=((LOOKUP(factor1))==1) ? LOOKUP(Factor0)* ([AUDIT_TRAIL].[INPUT_NON_RECURRING]) , [AUDIT_TRAIL].[INPUT_NON_RECURRING] : [AUDIT_TRAIL].[INPUT_NON_RECURRING],AUDIT_TRAIL=INPUT_NON_RECURRING)

*NEXT
*ENDWHEN 

but I keep getting this error in the UJKT

UJK_VALIDATION_EXCEPTION:Member "" not exist
former_member186338
Active Contributor

Sorry maleodillet !

You are trying to write some strange code, without clear explanation of required calculation logic!

Please read again: https://blogs.sap.com/2014/01/31/how-to-ask-questions-about-script-logic-issues/

In general, it's better to ask a new question and clearly explain the logic! I can't use your incorrect code as logic definition. Use words and samples.

P.S. By the way, LOOKUP can't be used inside FOR/NEXT loop!

P.P.S. And your LOOKUP syntax is strange: you don't need to scope not changed members...

Answers (2)

Answers (2)

former_member186338
Active Contributor
0 Kudos

If it's the same model, then without LOOKUP it will be:

*REC(EXPRESSION=([AUDIT_TRAIL].[EXCLUSION_FLAG_BASE]==1)? <expression if true> : <expression if false>,...)

...

But the scope of your script is not clear!

Please read also my blog: https://blogs.sap.com/2014/01/31/how-to-ask-questions-about-script-logic-issues/

maleodillet
Participant
0 Kudos

Hi Vadim,

we are in SAP BPC 10.1 NW

Please see my full code and scoping right below;

*XDIM_MEMBERSET COMMITMENT_ITEM AS %BSMEMBXCL%=BAS(FT10_CLASS.010,FT10_015_1.01,FT10_150_7)
*XDIM_MEMBERSET COMMITMENT_ITEM AS %BSMEMB%=BAS(%COMMITMENT_ITEM_SET%)
*XDIM_MEMBERSET COST_OBJECT AS %BSMEMBCO%=BAS(%COST_OBJECT_SET%)

*SELECT(%MEMBTTL%,ID,COMMITMENT_ITEM,[ID]=%BSMEMB% AND [ID]<>%BSMEMBXCL%)
*SELECT(%MEMBTTLCO%,ID,COST_OBJECT,[ID]=%BSMEMBCO%)
*SELECT(%RATE_VER%,[SOURCE],VERSION,ID=%VERSION_SET%)

*XDIM_MEMBERSET AUDIT_TRAIL = CURRENCY_MIX
*XDIM_MEMBERSET VERSION =%VERSION_SET%
*XDIM_MEMBERSET CURRENCY = BAS(TCURR)
*XDIM_ADDMEMBERSET CURRENCY = USD, LC
*XDIM_MEMBERSET COMMITMENT_ITEM=%MEMBTTL% 
*XDIM_MEMBERSET CONTENT = %CONTENT_SET%
*XDIM_MEMBERSET COST_OBJECT = %MEMBTTLCO%
*XDIM_MEMBERSET FUND = %FUND_SET%
*XDIM_MEMBERSET TIME = %TIME_SET%
*XDIM_MEMBERSET MEASURES = PERIODIC

*LOOKUP SUMMARY

*DIM Factor1:AUDIT_TRAIL = "INPUT_RECURRING"
*DIM Factor1:COMMITMENT_ITEM = COMMITMENT_ITEM.ID
*DIM Factor1:CONTENT =  %CONTENT_SET%
*DIM Factor1:COST_OBJECT = COST_OBJECT.ID
*DIM Factor1:CURRENCY = "LC"
*DIM Factor1:FUND = %FUND_SET%
*DIM Factor1:TIME = %TIME_SET%
*DIM Factor1:VERSION = %VERSION_SET%
*DIM Factor1:MEASURES = "PERIODIC"

*DIM Factor2:AUDIT_TRAIL = "INPUT_NON_RECURRING"
*DIM Factor2:COMMITMENT_ITEM = COMMITMENT_ITEM.ID
*DIM Factor2:CONTENT = %CONTENT_SET%
*DIM Factor2:COST_OBJECT = COST_OBJECT.ID
*DIM Factor2:CURRENCY = "LC"
*DIM Factor2:FUND = %FUND_SET%
*DIM Factor2:TIME = %TIME_SET%
*DIM Factor2:VERSION = %VERSION_SET%
*DIM Factor2:MEASURES = "PERIODIC"

*DIM EX_FLAGMTD:AUDIT_TRAIL = "EXCLUSION_FLAG_BASE"
*DIM EX_FLAGMTD:COMMITMENT_ITEM = COMMITMENT_ITEM.ID
*DIM EX_FLAGMTD:CONTENT = %CONTENT_SET%
*DIM EX_FLAGMTD:COST_OBJECT = COST_OBJECT.ID
*DIM EX_FLAGMTD:CURRENCY = "LC"
*DIM EX_FLAGMTD:FUND = %FUND_SET%
*DIM EX_FLAGMTD:TIME = %TIME_SET%
*DIM EX_FLAGMTD:VERSION = %VERSION_SET%
*DIM EX_FLAGMTD:MEASURES = "PERIODIC"

*ENDLOOKUP

*WHEN LOOKUP(EX_FLAGMTD)
*IS 1

*REC(EXPRESSION =[AUDIT_TRAIL].[CURRENCY_MIX] * LOOKUP(Factor1) , AUDIT_TRAIL = "INPUT_RECURRING", VERSION = %VERSION_SET%,FUND = %FUND_SET%, CONTENT = %CONTENT_SET%)

*REC(EXPRESSION =[AUDIT_TRAIL].[CURRENCY_MIX] * LOOKUP(Factor2) , AUDIT_TRAIL = "INPUT_NON_RECURRING", VERSION = %VERSION_SET%,FUND = %FUND_SET%, CONTENT = %CONTENT_SET%)

*ENDWHEN 

former_member186338
Active Contributor
0 Kudos

The following syntax:

*WHEN LOOKUP(EX_FLAGMTD)
*IS 1

Is not supported!

You have to use ternary operator inside REC:

*REC(EXPRESSION=(LOOKUP(EX_FLAGMTD)==1) ? <expression if true> : <expression if false>, ...)

And:

1. Not sure that you need lookup! Do you requesting data for the same model? SUMMARY?

2. Your lookup statement is absolutely strange...

maleodillet
Participant
0 Kudos

Hi Vadim,

Thanks for your response. Yes, I am requesting Data for the same model.

former_member186338
Active Contributor
0 Kudos

2 maleodillet

Once again! What is your system: BPC MS or NW??????