How to optimizate Script Logic Loockup BPC verision 10.1 Standard

[former_member591588]

I need that a Script Logic excecute and provide the values needed fot the calculation.

The Script is:

//first part ***************************

*SELECT(%PER%,"[ID]",TIEMPO,"[LEVEL]=MONTH")

*LOOKUP Rate

*DIM FXTASA = TPM

*DIM MONEDAENTRADA = COP

*DIM FXSOCIEDAD = GLOBAL

*DIM VERSION = REAL

*FOR %PER_TC% = %PER%

*DIM P_%PER_TC%:TIEMPO="%PER_TC%"

*NEXT

*ENDLOOKUP

//second part **************************

*XDIM_MEMBERSET CLASEACTIVO = NO_APLICA

*XDIM_MEMBERSET MONEDA = ML

*XDIM_MEMBERSET MOVIMIENTO = BAS(MOV_PER) // this statement generate different period concepts, for example (2017.12, 2018.01, 2018.02........2040.12)

*XDIM_MEMBERSET INDICADORES = MOV_MES

*XDIM_MEMBERSET TIEMPO = %TIEMPO_SET%

*XDIM_MEMBERSET SOCIEDAD = %SOCIEDAD_SET%

*XDIM_MEMBERSET VERSION = %VERSION_SET%

*XDIM_MEMBERSET CUENTA = BAS(3)

*XDIM_MEMBERSET FLUJO = FL_000

*XDIM_MEMBERSET MEASURES = YTD

*WHEN CUENTA *IS <> BAS(3815)

*FOR %PER_TC% = %PER%

*REC(EXPRESSION = %VALUE% * LOOKUP(P_%PER_TC%), INDICADORES = MOV_MES, MONEDA = COP, TIEMPO = %TIEMPO_SET%, FLUJO = FL_002)

*NEXT

*ENDWHEN

*COMMIT

The problem is that the LOOKUP provide for example 100 values and the second part of the script logic provide 5 values. In this case the REC provide 5 values incorrects, because is multiplying the 5 values with the sum of the 100 values or is the same the REC multiply 5 values for the 100 values found in the LOOKUP.

I only need multiply the 5 values provided for the second part of the script logic with the 5 values (rates) corresponding the LOOKUP.

For example the second part the script logic generate 1 value for the DIM MOVIMIENTO 2017.12, and I need this value is multiplied for the rate corresponding to the month 2017.12.

How can I to do if I need use only 5 rates to multiply the 5 values found in the second part to the script logic?? How can I improve this script logic???

[former_member186338 · Accepted solution]

Script based on MOVIMIENTO

*LOOKUP Rate
*DIM FXTASA = TPM
*DIM MONEDAENTRADA = COP
*DIM FXSOCIEDAD = GLOBAL
*DIM VERSION = REAL
*DIM COP:TIEMPO=MOVIMIENTO.ID //Use MOVIMIENTO member ID of current record in WHEN/ENDWHEN
*ENDLOOKUP

*XDIM_MEMBERSET CLASEACTIVO = NO_APLICA
*XDIM_MEMBERSET MONEDA = ML
*XDIM_MEMBERSET MOVIMIENTO = BAS(MOV_PER) // this statement generate different period concepts, for example (2017.12, 2018.01, 2018.02........2040.12)
*XDIM_MEMBERSET INDICADORES = MOV_MES
*XDIM_MEMBERSET TIEMPO = %TIEMPO_SET%
*XDIM_MEMBERSET SOCIEDAD = %SOCIEDAD_SET%
*XDIM_MEMBERSET VERSION = %VERSION_SET%
*XDIM_MEMBERSET CUENTA = BAS(3)
*XDIM_MEMBERSET FLUJO = FL_000
*XDIM_MEMBERSET MEASURES = YTD
*WHEN CUENTA 
*IS <> BAS(3815)
*REC(EXPRESSION = %VALUE% * LOOKUP(COP), MONEDA = COP, FLUJO = FL_002)
*ENDWHEN 

It's not a bad idea to read help on LOOKUP: https://help.sap.com/viewer/a2049170bfeb4178ace32222842c3ec1/10.1/en-US/f02a3d4897254042be24ebea3ba5...

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

In you sample you didn't explain where do you want to store the result...

[former_member591588]

Hi Vadim,

Thank you for your help. I proved this script logic and works correctly.

I have a question: what do you mean with "Use MOVIMIENTO member ID of current record in WHEN/ENDWHEN"?

Is necesary include MOVIMIENTO member ID in the WHEN / ENDWHEN?. And if so, how should I inclue the dimension MOVIMIENTO y the statement WHEN?

The best regards,

Jeison

[former_member186338]

First - according to forum rules you have to "Accept" the correct answer.

Second: "Is necesary include MOVIMIENTO member ID in the WHEN / ENDWHEN?. And if so, how should I inclue the dimension MOVIMIENTO y the statement WHEN?" - No, No!

Each record processed by when/endwhen has all dimension members automatically, you don't need to mention MOVIMIENTO in this case.

[former_member186338]

P.S. "In you sample you didn't explain where do you want to store the result..." - not answered.

I assume that the result has to be stored in current TIEMPO member with current MOVIMIENTO member.

[former_member591588]

Ah ok, the result must be store un current TIEMPO member and corresponding MOVIMIENTO member.

For example, in this case the result must be store in TIEMPO 2018.01 and MOVIMIENTO 2016.12, 2017.03 and 2017.12. All values in the same month (2018.01).

[former_member186338]

Then the script is OK!

[former_member591588]

Hi Vadim,

Thank for you answere. Your script is correct but not is it that I need. Your script is useful to perform the conversion using a single rate to one month, but I need use several rates for several months.

The dimension MOVIMIENTO is used to classify records in a time concept, for example 2017.12. A record can be of the month 2018.01 but with MOVIMIENTO of 2017.12. I require a rate for the MOVIMIENTO 2017.12 not for the month 2018.01. I don´t need a rate according to time, I need a rate according to MOVIMIENTO.

In the second part of the script logic there are two dimension with time: TIEMPO and MOVIMIENTO, but Dim MOVIMIENTO is the dimension that is used to find the rate to generate the conversion of the value. For that reason is tha I was generate the LOOKUP with a FOR to change the TIME, so that all rates are consulted. But I need use only the rates necesary to the values generate in the second part of the script logic.

For example in a month (2018.01. Dim TIEMPO) is possible to have 3 records (3 values), with the same month but with different MOVIMIENTO (2017.12, 2016.12, 2015.12). In this case, I need to find 3 rates: a rate to 2017.12, a rate to 2016.12 and a rate to 2015.12. Your script only allows to find a rate for the month 2018.01 and with this rate excecute the conversion of the 3 values.

This image show how I need to do the conversion.

[former_member186338]

It's better to explain everything from the very beginning 🙂

Read my second answer!

[former_member186338]

LOOKUP is working this way:

It will use dimensions directly specified using *DIM statement inside LOOKUP.

If dimension is not specified by *DIM, then it's member will be delivered from the current record of WHEN/ENDWHEN loop.

The script will be:

*LOOKUP Rate
*DIM FXTASA = TPM
*DIM COP:MONEDAENTRADA = COP
*DIM FXSOCIEDAD = GLOBAL
*DIM VERSION = REAL
*ENDLOOKUP

*XDIM_MEMBERSET CLASEACTIVO = NO_APLICA
*XDIM_MEMBERSET MONEDA = ML
*XDIM_MEMBERSET MOVIMIENTO = BAS(MOV_PER) // this statement generate different period concepts, for example (2017.12, 2018.01, 2018.02........2040.12)
*XDIM_MEMBERSET INDICADORES = MOV_MES
*XDIM_MEMBERSET TIEMPO = %TIEMPO_SET%
*XDIM_MEMBERSET SOCIEDAD = %SOCIEDAD_SET%
*XDIM_MEMBERSET VERSION = %VERSION_SET%
*XDIM_MEMBERSET CUENTA = BAS(3)
*XDIM_MEMBERSET FLUJO = FL_000
*XDIM_MEMBERSET MEASURES = YTD
*WHEN CUENTA 
*IS <> BAS(3815)
*REC(EXPRESSION = %VALUE% * LOOKUP(COP), MONEDA = COP, FLUJO = FL_002)
*ENDWHEN 

I have removed useless statements. Never use *COMMIT.