Script Logic - Lookup multiple variables

[former_member192555]

Hi,

I am trying to make simple conversion logic following one blog on SCN. The problem is that the value for last variable in LOOKUP Rates is always overwriting the previous. In the examples below are with EUR and USD. What should be the correct code?

LGX:

*XDIM_MEMBERSET ENTITY = DE

*XDIM_MEMBERSET CATEGORY = Actual

*XDIM_MEMBERSET TIME = 2015.01

*XDIM_MEMBERSET RPTCURRENCY = LC

*LOOKUP Rates

*DIM R_ENTITY = "Global"

*DIM SOURCECUR:INPUTCURRENCY = ENTITY.CURRENCY

*DIM DESTCURR1:INPUTCURRENCY = USD

*DIM DESTCURR2:INPUTCURRENCY = EUR

*DIM R_ACCOUNT = ACCOUNT.RATETYPE

*ENDLOOKUP

*WHEN ACCOUNT.RATETYPE

*IS AVG,CLO

*REC(FACTOR = LOOKUP(DESTCURR1),RPTCURRENCY = USD)

*REC(FACTOR = LOOKUP(DESTCURR2),RPTCURRENCY = EUR)

*ENDWHEN

*COMMIT

-------------------------------------------------------------------------------------------------------------------------------------

LOG:

LOG BEGIN TIME:2015-05-26 10:34:32

FILE:\ROOT\WEBFOLDERS\TEST_CONSOL \ADMINAPP\Consolidation\TEST.LGF

USER:BPC

APPSET:TEST_CONSOL

APPLICATION:Consolidation

[INFO] GET_DIM_LIST(): I_APPL_ID="Consolidation", #dimensions=10

ACCOUNT,AUDITTRAIL,CATEGORY,ENTITY,FLOW,INTERCO,MEASURES,RPTCURRENCY,SCOPE,TIME

#dim_memberset=4

ENTITY:DE,1 in total.

CATEGORY:Actual,1 in total.

TIME:2015.01,1 in total.

RPTCURRENCY:LC,1 in total.

REC :%value%*(LOOKUP(DESTCURR1))

REC :%value%*(LOOKUP(DESTCURR2))

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 0.00 ms. 14  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY LOOKUP DATA FROM APPLICATION: Rates

QUERY TIME : 0.00 ms. 2  RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :0.00 ms.

28  RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 28  RECORDS ARE GENERATED.

DATA TO WRITE BACK:

ACCOUNT AUDITTRAIL CATEGORY ENTITY FLOW INTERCO RPTCURRENCY SCOPE TIME SIGNEDDATA

BS111 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

BS121 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

BS122 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

BS211 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

BS212 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

BS213 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

BS214 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

PL010 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

PL020 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

PL110 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

PL120 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

PL310 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

PL320 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

PL330 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

BS111 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

BS121 Input Actual DE Balance ThirdParty EUR S_None 2015.01 138.75

BS122 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

BS211 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

BS212 Input Actual DE Balance ThirdParty EUR S_None 2015.01 - 138.75

BS213 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

BS214 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

PL010 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

PL020 Input Actual DE Balance ThirdParty EUR S_None 2015.01 - 133.20

PL110 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

PL120 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

PL310 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

PL320 Input Actual DE Balance ThirdParty EUR S_None 2015.01 133.20

PL330 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

28  RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :1.00  ms.

SCRIPT RUNNING TIME IN TOTAL:1.00 s.

LOG END TIME:2015-05-26 10:34:33

--------------------------------

LGX:

*XDIM_MEMBERSET ENTITY = DE

*XDIM_MEMBERSET CATEGORY = Actual

*XDIM_MEMBERSET TIME = 2015.01

*XDIM_MEMBERSET RPTCURRENCY = LC

*LOOKUP Rates

*DIM R_ENTITY = "Global"

*DIM SOURCECUR:INPUTCURRENCY = ENTITY.CURRENCY

*DIM DESTCURR2:INPUTCURRENCY = EUR

*DIM DESTCURR1:INPUTCURRENCY = USD

*DIM R_ACCOUNT = ACCOUNT.RATETYPE

*ENDLOOKUP

*WHEN ACCOUNT.RATETYPE

*IS AVG,CLO

*REC(FACTOR = LOOKUP(DESTCURR1),RPTCURRENCY = USD)

*REC(FACTOR = LOOKUP(DESTCURR2),RPTCURRENCY = EUR)

*ENDWHEN

*COMMIT

-------------------------------------------------------------------------------------------------------------------------------------

LOG:

LOG BEGIN TIME:2015-05-26 10:39:51

FILE:\ROOT\WEBFOLDERS\TEST_CONSOL \ADMINAPP\Consolidation\TEST.LGF

USER:BPC

APPSET:TEST_CONSOL

APPLICATION:Consolidation

[INFO] GET_DIM_LIST(): I_APPL_ID="Consolidation", #dimensions=10

ACCOUNT,AUDITTRAIL,CATEGORY,ENTITY,FLOW,INTERCO,MEASURES,RPTCURRENCY,SCOPE,TIME

#dim_memberset=4

ENTITY:DE,1 in total.

CATEGORY:Actual,1 in total.

TIME:2015.01,1 in total.

RPTCURRENCY:LC,1 in total.

REC :%value%*(LOOKUP(DESTCURR1))

REC :%value%*(LOOKUP(DESTCURR2))

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 0.00 ms. 14  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY LOOKUP DATA FROM APPLICATION: Rates

QUERY TIME : 0.00 ms. 2  RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :0.00 ms.

28  RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 28  RECORDS ARE GENERATED.

DATA TO WRITE BACK:

ACCOUNT AUDITTRAIL CATEGORY ENTITY FLOW INTERCO RPTCURRENCY SCOPE TIME SIGNEDDATA

BS111 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

BS121 Input Actual DE Balance ThirdParty USD S_None 2015.01 133.20

BS122 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

BS211 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

BS212 Input Actual DE Balance ThirdParty USD S_None 2015.01 - 133.20

BS213 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

BS214 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

PL010 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

PL020 Input Actual DE Balance ThirdParty USD S_None 2015.01 - 144.30

PL110 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

PL120 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

PL310 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

PL320 Input Actual DE Balance ThirdParty USD S_None 2015.01 144.30

PL330 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

BS111 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

BS121 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

BS122 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

BS211 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

BS212 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

BS213 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

BS214 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

PL010 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

PL020 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

PL110 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

PL120 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

PL310 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

PL320 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

PL330 Input Actual DE Balance ThirdParty EUR S_None 2015.01 0.00

28  RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :1.00  ms.

SCRIPT RUNNING TIME IN TOTAL:1.00 s.

LOG END TIME:2015-05-26 10:39:52

[former_member192555 · Accepted solution]

I am continuing working on the code with try and error and in the same time learning Logic Script.

The code below is working with exception TMVL in bold. Is there a working alternative?

btw. If I set %TIME_SET% = 2011.12, 2010.12, it is working fine, but this is not the main idea. I would like to use Lookup rates from previous year for further calculations.

*XDIM_MEMBERSET COMPANY = %COMPANY_SET%

*XDIM_MEMBERSET C_CATEGORY = %C_CATEGORY_SET%

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET RPTCURRENCY = LC

*LOOKUP Rates

       *DIM R_ENTITY = "Global"

   *FOR %TIM% = %TIME_SET%, TMVL(-12, %TIME_SET%)

       *FOR %CUR% = %RPTCURRENCY_SET%

           *DIM C_%CUR%_%TIM%:INPUTCURRENCY = "%CUR%"            

       *NEXT

   *NEXT

       *DIM CLC:INPUTCURRENCY = COMPANY.CURRENCY

   *DIM R_ACCT = C_ACCT.RATETYPE

   *DIM MEASURES = PERIODIC

*ENDLOOKUP

*WHEN C_ACCT.RATETYPE

   *IS "AVG"

               *FOR %T% = %TIME_SET%, TMVL(-12, %TIME_SET%)

                   *FOR %C% = %RPTCURRENCY_SET%

                            *REC(FACTOR = LOOKUP(C_%C%_%T%)*LOOKUP(CLC),RPTCURRENCY = "%C%", TIME = "%T%")

                   *NEXT

              *NEXT

*ENDWHEN

*WHEN C_ACCT.RATETYPE

   *IS "AVG"

   *REC(FACTOR = 1,RPTCURRENCY = COMPANY.CURRENCY)

*ENDWHEN

[former_member186338 · Accepted solution]

Sorry, but can you explain the logic???

"If I set %TIME_SET% = 2011.12, 2010.12, it is working fine, but this is not the main idea. I would like to use Lookup rates from previous year for further calculations." - not clear!

Please read the help for TMVL: TMVL Parameter - SAP Business Planning and Consolidation, version for SAP NetWeaver - SAP Library

• variables, like %TIME_SET% 

  The first period of the TIME_SET is used as the base period for a negative offset and the last period of the TIME_SET is used as the base period for a positive offset.

*FOR %TIM% = %TIME_SET%, TMVL(-12, %TIME_SET%)

will result:

2010.12, 2011.12, TMVL(-12, 2010.12)

2010.12, 2011.12, 2009.12

Vadim

P.S. The idea to use TIME related labels in the LOOKUP is absolutely bad! PLEASE EXPLAIN THE LOGIC - WITHOUT ANY CODE!!!!!

[former_member192555 · Accepted solution]

Hi Vadim,

I would like to make such a code to do currency translation similar to the one done in BPC440 through business rules.

Here are the business rules for one RATETYPE = ENDFLOW.

OPEEND = the end rate of last year period. OPEEND is not separate R_ACCT, but it should get the END R_ACCT rate from last period of previous year.

So If I set %TIME_SET% = 2011.12, 2010.12 and use TMVL I want system to select 2011.12,2010.12 and 2009.12 as you described. Unfortunately, I am not able to use TMVL on those places where I putted them as the system is triggering exceptions.

Further considering the phrase "last period" of previous year, probably the TMVL will not be the best option. Something like %YEAR%(-1).12 where %YEAR% is not showing the system year, but instead the previous year to those set in %TIME_SET%.

When I am typing now, I am thinking about Select statement deriving the year from the %TIME_SET% and then adding .12 to the variable which I hopefully will be able to use in the FOR/NEXT loops.

Of course there are workarounds as for example:

- Use more members in R_ACCT which will replace OPEEND and OPEAVG and remove necessity of querying in current and previous periods

btw. I really appreciate your help Vadim for sharing your time and knowledge with me, but I don't think capital letters and exclamation marks are really necessary in these cases.

Best Regards,

Emiliyan

[former_member186338 · Accepted solution]

"I would like to make such a code to do currency translation similar to the one done in BPC440 through business rules." - absolutely strange idea...

"I am not able to use TMVL on those places where I putted them as the system is triggering exceptions." - may be something wrong with your system setup?

"Something like %YEAR%(-1).12 where %YEAR% is not showing the system year, but instead the previous year to those set in %TIME_SET%" - (-1) will work only for special variable %YEAR%

"When I am typing now, I am thinking about Select statement deriving the year from the %TIME_SET%" - the code will crash if 2 members with different years will be selected in %TIME_SET%.

"but I don't think capital letters and exclamation marks are really necessary in these cases." - unfortunately I have to ask you multiple times to explain the logic and still get close to nothing: "do currency translation similar to the one done in BPC440 through business rules"!

Vadim

[former_member192555 · Accepted solution]

Hi Vadim,

1. The reason for doing currency translation similar to one done by business rules is only for self-training. I learned a lot based on the fact that these code lines are the first other the those copied/pasted during the BPC420 Course. Otherwise, you are right, it does not make any sense at all to do fx translations through script logic if it can be done through business rules.

2. Are you able to use TMVL in FOR/NEXT in your setup? For line 9 in the script a paste it above I get following:

   *FOR %TIM% = %TIME_SET%, TMVL(-12, %TIME_SET%)

3. That was helpful - Regarding the *Select and crashing. Thanks!

4. Here is what I have if it is helpful to you:

Rates Model data:

LEGAL Model Data:

When Account Propery for RATETYPE is ENDFLOW, then I should apply the same logic in the script logic as the one in the the following business rules:

Right now I am stuck on LOOKUP. When I find a way to extract properly the required data from Rates model, then I will move to *When/IS/REC/ENDWHEN statements to apply the logic above.

Thanks,

Emiliyan

[former_member186338 · Accepted solution]

2. As far as I remember it's not possible to use TMVL like in your post, but I see no reason at all to use FOR/NEXT by TIME dimension here.

4. I do not want to spend time trying to understand what rule you want to simulate. Please explain the logic as a table: original data (amount and rates) -> calculation logic (pseudo-formula) -> required result.

Vadim

[former_member192555 · Accepted solution]

Hi Vadim,

Let me try to explain it different way. There are two major steps and 1 for correcting the company currency data region.

• Step 1: Get current(selected) and previous years' AVG and END rates. Also get the same rates for the ENTITY.CURRENCY

Example:

Input

TIME = 2011.12

CURRENCY = USD

COMPANY.CURRENCY = EUR

Expected data that will be used in next step from Lookup.

USD_AVG_2011.12 - 1

USD_AVG_2010.12 - 1

USD_END_2011.12 - 1

USD_END_2010.12 - 1

ENTITY.CURRENCY_AVG_2011.12 - 1.5
ENTITY.CURRENCY_AVG_2010.12 - 1

ENTITY.CURRENCY_END_2011.12 - 1.3

ENTITY.CURRENCY_END_2010.12 - 1.2

• Step 2: Perrform calculations based on FLOW ID and Account RATETYPE using the rates from Step1.

Sample Data:

Account                                   Flow          Currency         Time                  SIGNEDDATA

Machinery & Equipment          F_OB          LC               2011.12                    100 000.00

Machinery & Equipment          F_AQ          LC               2011.12                      20 000.00

Machinery & Equipment          F_CB          LC               2011.12                    120 000.00

*When ACCOUNT.RATETYPE = ENDFLOW

     *WHEN FLOW = F_OB

               %VALUE% for 2011.12 multiply by the END rates from 2010.12

                  Caluclation = 100000*1*1.2 = 12 000

     *WHEN FLOW = F_OB

               %VALUE% for 2011.12 multiply by the END rates from (2011.12 - 2010.12). Save the result in FLOW = F_TROB

                    Calculation - 100000*(1*1.3-1*1.2) = 10000 (F_TROB)

     *WHEN FLOW = F_ACQ

               %VALUE% for 2011.12 multiply by the AVG rates from 2011.12

                    Calculation - 20000*(1*1.5) = 30000

     *WHEN FLOW = F_ACQ

               %VALUE% for 2011.12 multiply by the (AVG-END) rates from 2011.12. Save the result in  Flow F_TRACQ.

                    Calculation - 20000*(1*1.3-1*1.5) = -4000

     *WHEN FLOW = F_CB

               %VALUE% for 2011.12 multiply by the (END) rates from 2011.12

                    Calculation - 120000*(1*1.3) = 156000

• Step 3

Remove the translations in Company_currency and make it the same as Local Currency. I am using following code:

*WHEN ENTITY.CURRENCY

   *IS USD,EUR

   *REC(FACTOR = 1,RPTCURRENCY = ENTITY.CURRENCY)

*ENDWHEN

Summary of the expected result:

[former_member186338 · Accepted solution]

I recommend you to minimize code just to the single case!

Trying to translate:

1. You have some value in LC: 100 000 with FLOW=F_OB, ACCOUNT=Machinery & Equipment (ACCOUNT.RATETYPE = ENDFLOW for this account), TIME=2011.12. Question - what time dimension members will be involved? Single month, number of months or all months of some year? What about entity? Can be multiple entities? Let's assume that for this value the company CURRENCY property is EUR.

2. You have rates:

USD_AVG_2011.12 - 1

USD_AVG_2010.12 - 1

USD_END_2011.12 - 1

USD_END_2010.12 - 1

EUR_AVG_2011.12 - 1.5
EUR_AVG_2010.12 - 1

EUR_END_2011.12 - 1.3

EUR_END_2010.12 - 1.2

3. You want to calculate:

%VALUE% * USD_END_2010.12 * EUR_END_2010.12 = 100 000 * 1 * 1.2 = 120 000

Explain the logic of TIME selection for rate!

Where to store result?

What is the business value of the result?

Vadim

[former_member192555 · Accepted solution]

Following your points Vadim:

1. Usually, such translations are expected to happen month by month. So I would expect only one month to be in the %TIME_SET% variable. But it could be any month from 1 to 12. If it could work for more than one month, it will be additional value.

Entities in most cases will be one by one, but if it could rune for more, then it will be nice feature.

3. Calculation

The Data Region is:

TIME = 2011.12

CATEGORY = ACTUAL

ENTITY = DE

RPTCURRENCY = LC(Memberset hardcoded)  / USD(DM Package Variable) for translation

For opening balances I want to have following calculations:

1. %VALUE%(2011.12)*END_RATES(2010.12)

It should not change the destination with exception of Currency (From LC to USD)

2. %VALUE%(2011.12)*END_RATES(2011.12-2010.12)

It should not change the destination with with exception of Currency(From LC to USD) and FLOW(From F_OB to F_TROB)

Business justification:

When you have opening balance as in this case, you should be aware already that it is equal to the closing balance of previous year. The closing balance of previous year was translated by the END Rate of previous year. When you create translations for current year with the above calculations you will leave the opening and closing balances equal and you will be able to track properly the exchange rate gains and losses.

If you look at the Expected result once again, you will see that the closing balance will be translated by the END rate of the current year and in the same time following equitation will still be intact:

Closing balance = Openning Balance + OB translation differences + Acquisitions + Acq. translation differences

[former_member186338 · Accepted solution]

1. %VALUE%(2011.09)*END_RATES(20??.??) - explain time logic in red! Get rate of the same month of prev year?

what do you mean by END_RATES? In prev post it looks like USD_END * EUR_END assuming EUR is LC

It should not change the destination with exception of Currency (From LC to USD) - OK, USD is the destination!

Vadim

[former_member192555 · Accepted solution]

1. %VALUE%(2011.09)*END_RATES(20??.??)

Yes in this case it is the same month of prev year, but it should be always the last month of the prev year. Something for which TMVL will probably not work straightforwardly as I wrote earlier with (-12, value).

END_RATES was just for simplification, in this particular case it should be:

%VALUE%(2011.09)*USD_END_2010.12*EUR_END_2010.12

Yep, EUR = LC

[former_member186338 · Accepted solution]

Then, create a property in TIME dimension: LASTMNTHPYEAR and fill it for all base members (not a big issue):

2011.01 - 2010.12

...

2011.12 - 2010.12

...

Then you can use LOOKUP like:

*LOOKUP Rates

*FOR %CUR% = %RPTCURRENCY_SET%

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

*DIM C_%CUR%:TIME=TIME.ID

*DIM P_%CUR%:INPUTCURRENCY = "%CUR%"           

*DIM P_%CUR%:TIME=TIME.LASTMNTHPYEAR

*NEXT

*DIM CLC:INPUTCURRENCY = COMPANY.CURRENCY

*DIM CLC:TIME=TIME.ID

*DIM PLC:INPUTCURRENCY = COMPANY.CURRENCY

*DIM PLC:TIME=TIME.LASTMNTHPYEAR

*DIM R_ENTITY = "Global"

*DIM R_ACCT = C_ACCT.RATETYPE

*ENDLOOKUP

Then LOOKUP(P_USD) and LOOKUP(PLC) - will get the value from last month of the previous year! LOOKUP(C_USD) and LOOKUP(CLC) - from current month and current year...

Vadim

P.S. Ensure that RATETYPE contain the correct rate account...

[former_member192555 · Accepted solution]

Thanks. I was looking for a way without using additional property, but this is nice too.

The definition of TIME for every variable is new knowledge to me!

*%CUR%:TIME=*

....

[former_member186338 · Accepted solution]

Well, *DIM XXX:TIME=TIME.ID is not required - it will happen by default. I have added it to have a clear picture. If you want to work without properties - then use business rules

TMVL is a bad solution - requires complex FOX/NEXT loops etc...

With my proposal you will have only single loop for different currencies.

Vadim

P.S. And maintenance of additional property for TIME dimension is a simple job - not a lot of members, etc...

[former_member192555]

I am also having troubles to set Dynamic variable in For/Next and then use it in REC.

*XDIM_MEMBERSET COMPANY = %COMPANY_SET%

*XDIM_MEMBERSET C_CATEGORY = %C_CATEGORY_SET%

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET RPTCURRENCY = %RPTCURRENCY_SET%

*LOOKUP Rates

      *DIM R_ENTITY = "Global"

      *FOR %CUR% = USD,EUR

      *DIM C_%CUR%:INPUTCURRENCY = %CUR%                

                 *NEXT

      *DIM R_ACCT = C_ACCT.RATETYPE

                *DIM MEASURES=PERIODIC

*ENDLOOKUP

*WHEN C_ACCT.RATETYPE

  *IS "AVG","ENDFLOW"

             *REC(FACTOR = LOOKUP(C_%CUR%),RPTCURRENCY = "%CUR%")

*ENDWHEN

It gives me:

UJK_VALIDATION_EXCEPTION:LOOKUP ID "C_%CUR%" not found

[former_member186338]

You have to use second FOR/NEXT loop with different loop variable - like:

*WHEN C_ACCT.RATETYPE

  *IS "AVG","ENDFLOW"

         *FOR %C% = USD,EUR   

         *REC(FACTOR = LOOKUP(C_%C%),RPTCURRENCY = "%C%")

         *NEXT

*ENDWHEN

Vadim

P.S. And scope currency to LC...

[former_member192555]

Thank you Vadim. Now the script runs, but I get the same problem as without FOR/NEXT loop. If I change the order of currencies in - *FOR %CUR% = EUR,USD, then USD is translated, but EUR is nulled.

Code:

*XDIM_MEMBERSET COMPANY = %COMPANY_SET%

*XDIM_MEMBERSET C_CATEGORY = %C_CATEGORY_SET%

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET RPTCURRENCY = %RPTCURRENCY_SET%

*LOOKUP Rates

      *DIM R_ENTITY = "Global"

      *FOR %CUR% = USD,EUR

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

      *NEXT

      *DIM R_ACCT = C_ACCT.RATETYPE

                 *DIM MEASURES = PERIODIC

*ENDLOOKUP

*WHEN C_ACCT.RATETYPE

  *IS "AVG","ENDFLOW"

                    *FOR %C% = USD,EUR

              *REC(FACTOR = LOOKUP(C_%C%),RPTCURRENCY = "%C%")

                    *NEXT

*ENDWHEN

LOG

LGX:

*XDIM_MEMBERSET COMPANY = <All>

*XDIM_MEMBERSET C_CATEGORY = ACTUAL

*XDIM_MEMBERSET TIME = 2011.12

*XDIM_MEMBERSET RPTCURRENCY = LC

*LOOKUP Rates

*DIM R_ENTITY = "Global"

*DIM C_USD:INPUTCURRENCY = "USD"

*DIM C_EUR:INPUTCURRENCY = "EUR"

*DIM R_ACCT = C_ACCT.RATETYPE

*DIM MEASURES = PERIODIC

*ENDLOOKUP

*WHEN C_ACCT.RATETYPE

*IS AVG,ENDFLOW

*REC(FACTOR = LOOKUP(C_USD),RPTCURRENCY = USD)

*REC(FACTOR = LOOKUP(C_EUR),RPTCURRENCY = EUR)

*ENDWHEN

-------------------------------------------------------------------------------------------------------------------------------------

LOG:

LOG BEGIN TIME:2015-05-31 22:53:43

FILE:\ROOT\WEBFOLDERS\BPC440_TEST \ADMINAPP\LEGAL\TEST.LGF

USER:BPC

APPSET:BPC440_TEST

APPLICATION:LEGAL

[INFO] GET_DIM_LIST(): I_APPL_ID="LEGAL", #dimensions=10

COMPANY,CONSGROUP,C_ACCT,C_CATEGORY,C_DATASRC,FLOW,INTCO,MEASURES,RPTCURRENCY,TIME

#dim_memberset=3

C_CATEGORY:ACTUAL,1 in total.

TIME:2011.12,1 in total.

RPTCURRENCY:LC,1 in total.

REC :%value%*(LOOKUP(C_USD))

REC :%value%*(LOOKUP(C_EUR))

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 0.00 ms. 35  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY LOOKUP DATA FROM APPLICATION: Rates

QUERY TIME : 0.00 ms. 3  RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :0.00 ms.

70  RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 70  RECORDS ARE GENERATED.

DATA TO WRITE BACK:

COMPANY CONSGROUP C_ACCT C_CATEGORY C_DATASRC FLOW INTCO RPTCURRENCY TIME SIGNEDDATA

C3000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C9000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C5100 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C1000 G_NONE CF001 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C2000 G_NONE 30311000 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C4000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C2000 G_NONE 12120300 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C2000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C5000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C2000 G_NONE 11211000 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C2000 G_NONE 12110300 ACTUAL INPUT F_ACQ I_NONE USD 2011.12 0.00

C2000 G_NONE 12110300 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C1000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C1000 G_NONE CF002 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C1000 G_NONE 30120000 ACTUAL INPUT F_CB I_C2000 USD 2011.12 0.00

C2000 G_NONE 39000000 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C1000 G_NONE 12110300 ACTUAL INPUT F_ACQ I_NONE USD 2011.12 0.00

C1000 G_NONE 11301100 ACTUAL INPUT F_CB I_C2000 USD 2011.12 0.00

C1000 G_NONE 21121000 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C1000 G_NONE 30527140 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C1000 G_NONE 12110300 ACTUAL INPUT F_OB I_NONE USD 2011.12 0.00

C1000 G_NONE 30110000 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C1000 G_NONE 30311000 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C2000 G_NONE 30312000 ACTUAL INPUT F_CB I_C1000 USD 2011.12 0.00

C2000 G_NONE 21171000 ACTUAL INPUT F_CB I_C1000 USD 2011.12 0.00

C2000 G_NONE 30110000 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C2000 G_NONE CF001 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C1000 G_NONE 11211000 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C1000 G_NONE 12120300 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C2000 G_NONE CF002 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C2000 G_NONE 30527140 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C2000 G_NONE 21121000 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C2000 G_NONE 12110300 ACTUAL INPUT F_OB I_NONE USD 2011.12 0.00

C1000 G_NONE 39000000 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C1000 G_NONE 12110300 ACTUAL INPUT F_CB I_NONE USD 2011.12 0.00

C3000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 91000.00

C9000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 91000.00

C5100 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 65000.00

C1000 G_NONE CF001 ACTUAL INPUT F_CB I_NONE EUR 2011.12 26000.00

C2000 G_NONE 30311000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 117000.00

C4000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 65000.00

C2000 G_NONE 12120300 ACTUAL INPUT F_CB I_NONE EUR 2011.12 - 2600.00

C2000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 49299.90

C5000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 91000.00

C2000 G_NONE 11211000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 15600.00

C2000 G_NONE 12110300 ACTUAL INPUT F_ACQ I_NONE EUR 2011.12 78000.00

C2000 G_NONE 12110300 ACTUAL INPUT F_CB I_NONE EUR 2011.12 104000.00

C1000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 115700.00

C1000 G_NONE CF002 ACTUAL INPUT F_CB I_NONE EUR 2011.12 6500.00

C1000 G_NONE 30120000 ACTUAL INPUT F_CB I_C2000 EUR 2011.12 - 36346.50

C2000 G_NONE 39000000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 30000.00

C1000 G_NONE 12110300 ACTUAL INPUT F_ACQ I_NONE EUR 2011.12 26000.00

C1000 G_NONE 11301100 ACTUAL INPUT F_CB I_C2000 EUR 2011.12 27300.00

C1000 G_NONE 21121000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 - 13000.00

C1000 G_NONE 30527140 ACTUAL INPUT F_CB I_NONE EUR 2011.12 7500.00

C1000 G_NONE 12110300 ACTUAL INPUT F_OB I_NONE EUR 2011.12 130000.00

C1000 G_NONE 30110000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 - 187500.00

C1000 G_NONE 30311000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 150000.00

C2000 G_NONE 30312000 ACTUAL INPUT F_CB I_C1000 EUR 2011.12 36346.50

C2000 G_NONE 21171000 ACTUAL INPUT F_CB I_C1000 EUR 2011.12 - 27300.00

C2000 G_NONE 30110000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 - 150000.00

C2000 G_NONE CF001 ACTUAL INPUT F_CB I_NONE EUR 2011.12 26000.00

C1000 G_NONE 11211000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 13000.00

C1000 G_NONE 12120300 ACTUAL INPUT F_CB I_NONE EUR 2011.12 - 6500.00

C2000 G_NONE CF002 ACTUAL INPUT F_CB I_NONE EUR 2011.12 2600.00

C2000 G_NONE 30527140 ACTUAL INPUT F_CB I_NONE EUR 2011.12 3000.00

C2000 G_NONE 21121000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 - 13000.00

C2000 G_NONE 12110300 ACTUAL INPUT F_OB I_NONE EUR 2011.12 26000.00

C1000 G_NONE 39000000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 30000.00

C1000 G_NONE 12110300 ACTUAL INPUT F_CB I_NONE EUR 2011.12 156000.00

70  RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :0.00  ms.

SCRIPT RUNNING TIME IN TOTAL:1.00 s.

LOG END TIME:2015-05-31 22:53:44

[former_member192555]

btw. The code is for different model than the one in the first post, but the logic applies.

Well, this code is working

DATA Region

COMPANY = <All>

C_CATEGORY = ACTUAL

Time = 2011.12

RPTCURRENCY = USD,EUR

Code:

*XDIM_MEMBERSET COMPANY = %COMPANY_SET%

*XDIM_MEMBERSET C_CATEGORY = %C_CATEGORY_SET%

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET RPTCURRENCY = LC

*LOOKUP Rates

      *DIM R_ENTITY = "Global"

      *FOR %CUR% = %RPTCURRENCY_SET%

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

      *NEXT

      *DIM CLC:INPUTCURRENCY = COMPANY.CURRENCY

      *DIM R_ACCT = C_ACCT.RATETYPE

       *DIM MEASURES = PERIODIC

*ENDLOOKUP

*WHEN C_ACCT.RATETYPE

  *IS "AVG","ENDFLOW"

                    *FOR %C% = %RPTCURRENCY_SET%

                          *REC(FACTOR = LOOKUP(C_%C%)/LOOKUP(CLC),RPTCURRENCY = "%C%")

                    *NEXT

*ENDWHEN

LOG

LGX:

*XDIM_MEMBERSET COMPANY = <All>

*XDIM_MEMBERSET C_CATEGORY = ACTUAL

*XDIM_MEMBERSET TIME = 2011.12

*XDIM_MEMBERSET RPTCURRENCY = LC

*LOOKUP Rates

*DIM R_ENTITY = "Global"

*DIM C_USD:INPUTCURRENCY = "USD"

*DIM C_EUR:INPUTCURRENCY = "EUR"

*DIM CLC:INPUTCURRENCY = COMPANY.CURRENCY

*DIM R_ACCT = C_ACCT.RATETYPE

*DIM MEASURES = PERIODIC

*ENDLOOKUP

*WHEN C_ACCT.RATETYPE

*IS AVG,ENDFLOW

*REC(FACTOR = LOOKUP(C_USD)/LOOKUP(CLC),RPTCURRENCY = USD)

*REC(FACTOR = LOOKUP(C_EUR)/LOOKUP(CLC),RPTCURRENCY = EUR)

*ENDWHEN

-------------------------------------------------------------------------------------------------------------------------------------

LOG:

LOG BEGIN TIME:2015-06-01 09:40:32

FILE:\ROOT\WEBFOLDERS\BPC440_TEST \ADMINAPP\LEGAL\TEST.LGF

USER:BPC

APPSET:BPC440_TEST

APPLICATION:LEGAL

[INFO] GET_DIM_LIST(): I_APPL_ID="LEGAL", #dimensions=10

COMPANY,CONSGROUP,C_ACCT,C_CATEGORY,C_DATASRC,FLOW,INTCO,MEASURES,RPTCURRENCY,TIME

#dim_memberset=3

C_CATEGORY:ACTUAL,1 in total.

TIME:2011.12,1 in total.

RPTCURRENCY:LC,1 in total.

REC :%value%*(LOOKUP(C_USD)/LOOKUP(CLC))

REC :%value%*(LOOKUP(C_EUR)/LOOKUP(CLC))

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 0.00 ms. 35  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY LOOKUP DATA FROM APPLICATION: Rates

QUERY TIME : 0.00 ms. 21  RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :0.00 ms.

70  RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 70  RECORDS ARE GENERATED.

DATA TO WRITE BACK:

COMPANY CONSGROUP C_ACCT C_CATEGORY C_DATASRC FLOW INTCO RPTCURRENCY TIME SIGNEDDATA

C3000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE USD 2011.12 70000.00

C9000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE USD 2011.12 70000.00

C5100 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE USD 2011.12 38461.54

C1000 G_NONE CF001 ACTUAL INPUT F_CB I_NONE USD 2011.12 15384.62

C2000 G_NONE 30311000 ACTUAL INPUT F_CB I_NONE USD 2011.12 52000.00

C4000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE USD 2011.12 38461.54

C2000 G_NONE 12120300 ACTUAL INPUT F_CB I_NONE USD 2011.12 - 1538.46

C2000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE USD 2011.12 29171.54

C5000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE USD 2011.12 53846.15

C2000 G_NONE 11211000 ACTUAL INPUT F_CB I_NONE USD 2011.12 9230.77

C2000 G_NONE 12110300 ACTUAL INPUT F_ACQ I_NONE USD 2011.12 46153.85

C2000 G_NONE 12110300 ACTUAL INPUT F_CB I_NONE USD 2011.12 61538.46

C1000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE USD 2011.12 68461.54

C1000 G_NONE CF002 ACTUAL INPUT F_CB I_NONE USD 2011.12 3846.15

C1000 G_NONE 30120000 ACTUAL INPUT F_CB I_C2000 USD 2011.12 - 16154.00

C2000 G_NONE 39000000 ACTUAL INPUT F_CB I_NONE USD 2011.12 13333.33

C1000 G_NONE 12110300 ACTUAL INPUT F_ACQ I_NONE USD 2011.12 15384.62

C1000 G_NONE 11301100 ACTUAL INPUT F_CB I_C2000 USD 2011.12 16153.85

C1000 G_NONE 21121000 ACTUAL INPUT F_CB I_NONE USD 2011.12 - 7692.31

C1000 G_NONE 30527140 ACTUAL INPUT F_CB I_NONE USD 2011.12 3333.33

C1000 G_NONE 12110300 ACTUAL INPUT F_OB I_NONE USD 2011.12 76923.08

C1000 G_NONE 30110000 ACTUAL INPUT F_CB I_NONE USD 2011.12 - 83333.33

C1000 G_NONE 30311000 ACTUAL INPUT F_CB I_NONE USD 2011.12 66666.67

C2000 G_NONE 30312000 ACTUAL INPUT F_CB I_C1000 USD 2011.12 16154.00

C2000 G_NONE 21171000 ACTUAL INPUT F_CB I_C1000 USD 2011.12 - 16153.85

C2000 G_NONE 30110000 ACTUAL INPUT F_CB I_NONE USD 2011.12 - 66666.67

C2000 G_NONE CF001 ACTUAL INPUT F_CB I_NONE USD 2011.12 15384.62

C1000 G_NONE 11211000 ACTUAL INPUT F_CB I_NONE USD 2011.12 7692.31

C1000 G_NONE 12120300 ACTUAL INPUT F_CB I_NONE USD 2011.12 - 3846.15

C2000 G_NONE CF002 ACTUAL INPUT F_CB I_NONE USD 2011.12 1538.46

C2000 G_NONE 30527140 ACTUAL INPUT F_CB I_NONE USD 2011.12 1333.33

C2000 G_NONE 21121000 ACTUAL INPUT F_CB I_NONE USD 2011.12 - 7692.31

C2000 G_NONE 12110300 ACTUAL INPUT F_OB I_NONE USD 2011.12 15384.62

C1000 G_NONE 39000000 ACTUAL INPUT F_CB I_NONE USD 2011.12 13333.33

C1000 G_NONE 12110300 ACTUAL INPUT F_CB I_NONE USD 2011.12 92307.69

C3000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 91000.00

C9000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 91000.00

C5100 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 50000.00

C1000 G_NONE CF001 ACTUAL INPUT F_CB I_NONE EUR 2011.12 20000.00

C2000 G_NONE 30311000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 78000.00

C4000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 50000.00

C2000 G_NONE 12120300 ACTUAL INPUT F_CB I_NONE EUR 2011.12 - 2000.00

C2000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 37923.00

C5000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 70000.00

C2000 G_NONE 11211000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 12000.00

C2000 G_NONE 12110300 ACTUAL INPUT F_ACQ I_NONE EUR 2011.12 60000.00

C2000 G_NONE 12110300 ACTUAL INPUT F_CB I_NONE EUR 2011.12 80000.00

C1000 G_NONE 11112000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 89000.00

C1000 G_NONE CF002 ACTUAL INPUT F_CB I_NONE EUR 2011.12 5000.00

C1000 G_NONE 30120000 ACTUAL INPUT F_CB I_C2000 EUR 2011.12 - 24231.00

C2000 G_NONE 39000000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 20000.00

C1000 G_NONE 12110300 ACTUAL INPUT F_ACQ I_NONE EUR 2011.12 20000.00

C1000 G_NONE 11301100 ACTUAL INPUT F_CB I_C2000 EUR 2011.12 21000.00

C1000 G_NONE 21121000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 - 10000.00

C1000 G_NONE 30527140 ACTUAL INPUT F_CB I_NONE EUR 2011.12 5000.00

C1000 G_NONE 12110300 ACTUAL INPUT F_OB I_NONE EUR 2011.12 100000.00

C1000 G_NONE 30110000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 - 125000.00

C1000 G_NONE 30311000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 100000.00

C2000 G_NONE 30312000 ACTUAL INPUT F_CB I_C1000 EUR 2011.12 24231.00

C2000 G_NONE 21171000 ACTUAL INPUT F_CB I_C1000 EUR 2011.12 - 21000.00

C2000 G_NONE 30110000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 - 100000.00

C2000 G_NONE CF001 ACTUAL INPUT F_CB I_NONE EUR 2011.12 20000.00

C1000 G_NONE 11211000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 10000.00

C1000 G_NONE 12120300 ACTUAL INPUT F_CB I_NONE EUR 2011.12 - 5000.00

C2000 G_NONE CF002 ACTUAL INPUT F_CB I_NONE EUR 2011.12 2000.00

C2000 G_NONE 30527140 ACTUAL INPUT F_CB I_NONE EUR 2011.12 2000.00

C2000 G_NONE 21121000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 - 10000.00

C2000 G_NONE 12110300 ACTUAL INPUT F_OB I_NONE EUR 2011.12 20000.00

C1000 G_NONE 39000000 ACTUAL INPUT F_CB I_NONE EUR 2011.12 20000.00

C1000 G_NONE 12110300 ACTUAL INPUT F_CB I_NONE EUR 2011.12 120000.00

70  RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :0.00  ms.

SCRIPT RUNNING TIME IN TOTAL:1.00 s.

LOG END TIME:2015-06-01 09:40:32

[former_member186338]

If you want to understand how it works - then use simple test with single record in LC and 2 exchange rates. Ensure that you have records and records are correct (using RSA1). Test in UJKT. Also look on resulting records in RSA1.

Vadim

[former_member186338]

Hi Emiliyan,

Try to use short label - like CUSD instead of DESTCURR1

Also, please eliminate zero's from the model...

And show initial figures in LC!

Vadim

[former_member186338]

P.S. And by the way, stop using absolutely useless *COMMIT!

[former_member192555]

Hi Vadim,

Thank you for the Commit advice. I have changed the Logic Script and here is the data. The problem is that during LOOKUP it is not getting several values with different members from one dimension - Currency. Whichever member is the last one, the system will get only it.  In this case EUR is the last member in Lookup and for that reason it is not getting the currency for USD exchange.

What is the work around for that?

LGX:

*XDIM_MEMBERSET ENTITY = DE

*XDIM_MEMBERSET CATEGORY = Actual

*XDIM_MEMBERSET TIME = 2015.01

*XDIM_MEMBERSET RPTCURRENCY = LC

*LOOKUP Rates

*DIM R_ENTITY = "Global"

*DIM SOURCECUR:INPUTCURRENCY = ENTITY.CURRENCY

*DIM DESTCURR1:INPUTCURRENCY = USD

*DIM DESTCURR2:INPUTCURRENCY = EUR

*DIM R_ACCOUNT = ACCOUNT.RATETYPE

*ENDLOOKUP

*WHEN ACCOUNT.RATETYPE

*IS AVG,CLO

*REC(FACTOR = LOOKUP(DESTCURR1),RPTCURRENCY = USD)

*REC(FACTOR = LOOKUP(DESTCURR2),RPTCURRENCY = EUR)

*ENDWHEN

-------------------------------------------------------------------------------------------------------------------------------------

LOG:

LOG BEGIN TIME:2015-05-29 19:56:55

FILE:\ROOT\WEBFOLDERS\TEST_SCRIPT \ADMINAPP\Consolidation\TEST.LGF

USER:BPC

APPSET:TEST_SCRIPT

APPLICATION:Consolidation

[INFO] GET_DIM_LIST(): I_APPL_ID="Consolidation", #dimensions=10

ACCOUNT,AUDITTRAIL,CATEGORY,ENTITY,FLOW,INTERCO,MEASURES,RPTCURRENCY,SCOPE,TIME

#dim_memberset=4

ENTITY:DE,1 in total.

CATEGORY:Actual,1 in total.

TIME:2015.01,1 in total.

RPTCURRENCY:LC,1 in total.

REC :%value%*(LOOKUP(DESTCURR1))

REC :%value%*(LOOKUP(DESTCURR2))

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 0.00 ms. 5  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY LOOKUP DATA FROM APPLICATION: Rates

QUERY TIME : 1.00 ms. 2  RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :0.00 ms.

10  RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 10  RECORDS ARE GENERATED.

DATA TO WRITE BACK:

ACCOUNT AUDITTRAIL CATEGORY ENTITY FLOW INTERCO RPTCURRENCY SCOPE TIME SIGNEDDATA

BS111 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

BS222 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

PL010 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

PL020 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

PL310 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

BS111 Input Actual DE Balance ThirdParty EUR S_None 2015.01 150.00

BS222 Input Actual DE Balance ThirdParty EUR S_None 2015.01 - 300.00

PL010 Input Actual DE Balance ThirdParty EUR S_None 2015.01 - 600.00

PL020 Input Actual DE Balance ThirdParty EUR S_None 2015.01 - 750.00

PL310 Input Actual DE Balance ThirdParty EUR S_None 2015.01 450.00

10  RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :0.00  ms.

SCRIPT RUNNING TIME IN TOTAL:1.00 s.

LOG END TIME:2015-05-29 19:56:55

[former_member186338]

Sorry, but:

I recommended:

"Try to use short label - like CUSD instead of DESTCURR1"

and what I see:

...

*REC(FACTOR = LOOKUP(DESTCURR1),RPTCURRENCY = USD)

...

Vadim

P.S. The idea was that may be only first 8 symbols in the label are significant!

[former_member192555]

Probably I did not copied the right thing after changes. Unfortunately, the same thing.

LGX:

*XDIM_MEMBERSET ENTITY = DE

*XDIM_MEMBERSET CATEGORY = Actual

*XDIM_MEMBERSET TIME = 2015.01

*XDIM_MEMBERSET RPTCURRENCY = LC

*LOOKUP Rates

*DIM R_ENTITY = "Global"

*DIM CLC:INPUTCURRENCY = ENTITY.CURRENCY

*DIM CUSD:INPUTCURRENCY = USD

*DIM CEUR:INPUTCURRENCY = EUR

*DIM R_ACCOUNT = ACCOUNT.RATETYPE

*ENDLOOKUP

*WHEN ACCOUNT.RATETYPE

*IS AVG,CLO

*REC(FACTOR = LOOKUP(CUSD),RPTCURRENCY = USD)

*REC(FACTOR = LOOKUP(CEUR),RPTCURRENCY = EUR)

*ENDWHEN

-------------------------------------------------------------------------------------------------------------------------------------

LOG:

LOG BEGIN TIME:2015-05-30 23:17:05

FILE:\ROOT\WEBFOLDERS\TEST_SCRIPT \ADMINAPP\Consolidation\TEST.LGF

USER:BPC

APPSET:TEST_SCRIPT

APPLICATION:Consolidation

[INFO] GET_DIM_LIST(): I_APPL_ID="Consolidation", #dimensions=10

ACCOUNT,AUDITTRAIL,CATEGORY,ENTITY,FLOW,INTERCO,MEASURES,RPTCURRENCY,SCOPE,TIME

#dim_memberset=4

ENTITY:DE,1 in total.

CATEGORY:Actual,1 in total.

TIME:2015.01,1 in total.

RPTCURRENCY:LC,1 in total.

REC :%value%*(LOOKUP(CUSD))

REC :%value%*(LOOKUP(CEUR))

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 2.00 ms. 5  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY LOOKUP DATA FROM APPLICATION: Rates

QUERY TIME : 1.00 ms. 2  RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :0.00 ms.

10  RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 10  RECORDS ARE GENERATED.

DATA TO WRITE BACK:

ACCOUNT AUDITTRAIL CATEGORY ENTITY FLOW INTERCO RPTCURRENCY SCOPE TIME SIGNEDDATA

BS111 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

BS222 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

PL010 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

PL020 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

PL310 Input Actual DE Balance ThirdParty USD S_None 2015.01 0.00

BS111 Input Actual DE Balance ThirdParty EUR S_None 2015.01 150.00

BS222 Input Actual DE Balance ThirdParty EUR S_None 2015.01 - 300.00

PL010 Input Actual DE Balance ThirdParty EUR S_None 2015.01 - 600.00

PL020 Input Actual DE Balance ThirdParty EUR S_None 2015.01 - 750.00

PL310 Input Actual DE Balance ThirdParty EUR S_None 2015.01 450.00

10  RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :1.00  ms.

SCRIPT RUNNING TIME IN TOTAL:7.00 s.

LOG END TIME:2015-05-30 23:17:06