on 03-11-2019 3:22 PM
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
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)
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
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...
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/
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
6 | |
5 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.