LOOKUP *FOR *NEXT "syntax error".

[adrian_pmelchor]

Hello.

My system is BPC 10 NW,

I'm trying to do a Script to achieve the next logic:

I have two Models (Contracts & Planning). Contracts has the next data

ACCOUNT_RS	TIME	SIGNED_DATA
PRICEM2	2014.UNITS	200
PRICEM2	2013.UNITS	150
PRICEM2	....	....

The other model (Planning) has the next data

ACCOUNT_RS	TIME	SIGNED_DATA
PERCENTAGEARENT	2014.JAN	0,5
PERCENTAGEARENT	2014.FEB	0,7
PERCENTAGEARENT	...	....
PERCENTAGEARENT	2013.JAN	0,6
PERCENTAGEARENT	2013.FEB	0,9
PERCENTAGEARENT	....	....

And the result (in model Planning) I am looking forward is:

ACCOUNT_RS	TIME	SIGNED_DATA
PERCENTAGEARENT	2014.JAN	0,5
PERCENTAGEARENT	2014.FEB	0,7
PERCENTAGEARENT	....	....
PERCENTAGEARENT	2013.JAN	0,6
PERCENTAGEARENT	2013.FEB	0,9
PERCENTAGEARENT	....	....
PRICEM2	2014.JAN	200
PRICEM2	2014.FEB	200
PRICEM2	...
PRICEM2	2013.JAN	150
PRICEM2	2013.FEB	150

The Script that I developed is the next but I get Syntax error in the line of *REC

//JAN, FEB ...

*SELECT(%MON_SEL%,MONTHNUM,TIME,LEVEL= MONTH)

//2014, 2015...

*SELECT(%YEA_SEL%,YEAR,TIME,LEVEL = YEAR)

*LOOKUP Contracts

  *FOR %YEA_ACT% = %YEA_SEL%

  *FOR %MON_ACT% = %MON_SEL%

  //TIM_2014.JAN, TIM_2014.FEB

  *DIM TIM_%YEA_ACT%.%MON_ACT%:TIME = %YEA_ACT%

  //ACS_2014.JAN, ACS_2014.FEB

  *DIM ACS_%YEA_ACT%.%MON_ACT%:ACCOUNT_RS = PRICEM2

  *NEXT

  *NEXT

*ENDLOOKUP

*WHEN ACCOUNT_RS

*IS PERCENTAGEARENT

  *FOR %YEA_ACT% = %YEA_SEL%

  *FOR %MON_ACT% = %MON_SEL%

  *WHEN TIME

  *IS %YEA_ACT%.%MON_ACT%

  //2014.JAN ..

  *REC(EXPRESION = LOKKUP(ACS_%YEA_ACT%.%MON_ACT%), ACCOUNT_RS = PRICEM2)

  *ENDWHEN

  *NEXT

  *NEXT

*ENDWHEN

[former_member186338 · Accepted solution]

Sorry,

But PERCENTAGEARENT is not changed in the result - why are you talking about it?

I also see that you want to simply push data from contractsto planning: from YYYY.UNITS to all months of YYYY

What is the business logic behind?

Vadim

[adrian_pmelchor · Accepted solution]

Exactly PERCENTAGEARENT doesn't change.

The business logic It's that I have a BADI that generates de values for the PERCENTAGEARENT depending on two dates (Date Begin and Date End). These dates are in CONTRACTS model.

In dimension ACCOUNT_RS (model CONTRACTS) I have two members DATE_BEGIN, DATE_END in wich I save the value of these dates.

For example if I have in DATE_BEGIN 01.01.2014 and in DATE_END 04.04.2014 I generate the values for PERCENTAGEARENT for the model PLANNING

ACCOUNT_RS	TIME	SIGNED_DATA
PERCENTAGEARENT	2014.JAN	1
PERCENTAGEARENT	2014.FEB	1
PERCENTAGEARENT	2014.MAR	1
PERCENTAGEARENT	2014.APR	0,12 (4/31)

Once the user is changing data in model PLANNING I have to read data from MODEL contracts in dimension ACCOUNT_RS member PRICEM2 and dimension TIME the corresponding year. Wich this I want in model PLANNING:

ACCOUNT_RS	TIME	SIGNED_DATA
PRICEM2	2014.JAN	200
PRICEM2	2014.FEB	200
PRICEM2	2014.MAR	200
PRICEM2	2014.APR	200

Once I get the values for dimension ACCOUNT_RS member PRICE_M2 I can calculate my corresponding value (PERCETAGEARENT * PRICEM2)

[former_member186338 · Accepted solution]

Still not clear:

"In dimension ACCOUNT_RS (model CONTRACTS) I have two members DATE_BEGIN, DATE_END in wich I save the value of these dates." - how do you store dates in SIGNED_DATA???

Please, provide a clear picture of all dimensions involved in each model with members etc...

"Once the user is changing data in model PLANNING I have to read data from MODEL contracts..."

- What data is changed in PLANNING?

- You want the script to be run in default.lgf - immediately after data changed???

Vadim

P.S. Sorry, by if you want to get a solution you HAVE to CLEARLY explain....

[adrian_pmelchor · Accepted solution]

Ok first I know that it's not a good practice to save dates in Signed Data but it's not mine decision.

The Fact Table of Model Contracts would be the next.

	CONTRACT
ACCOUNT_RS	TIME	SIGNED_DATA
DATE_BEGIN	DUMMY	41640 (01/01/2014)
DATE_END	DUMMY	41733 (04/04/2014)
PRICEM2	2014.UNITS	200

With the BADI  what I do it's  calculate the percentaje of the price per month depending of the number de day's of the month and the begin date and end date. These values are saved in model PLANNING

In the example the begin date its 01.01.2014 and the end dathe it's 04.04.2014. Then for each month

January = 31/31 = 1

February = 28/28 = 1

March  =  31/31 = 1

April = 4/31 = 0,12

	PLANNING
ACCOUNT_RS	TIME	SIGNED_DATA
PERCENTAGEARENT	2014.JAN	1
PERCENTAGEARENT	2014.FEB	1
PERCENTAGEARENT	2014.MAR	1
PERCENTAGEARENT	2014.APR	0,12

Now I have the percentages and the months in which I have to calculate my final value. Then what I need is to lookup for each month the value of the dimension PRICEM2 but looking in [YEAR].UNITS

This script should return the next values (omitting PERCENTAGEARENT that won't change)

	PLANNING
ACCOUNT_RS	TIME	SIGNED_DATA
PRICEM2	2014.JAN	200
PRICEM2	2014.FEB	200
PRICEM2	2014.MAR	200
PRICEM2	2014.APR	200

With these two values in model planning I would be able to make further calculates that I need.

[former_member210696 · Accepted solution]

Hi Adrian,

I am not sure if I have understood it correctly or not, but whatever I could understand is that you want to fetch data from Contracts model for 2013.UNITS, 2014.UNITS and likewise and then write the same data to Planning model (2013.UNITs to all members of 2013.total in planning). If that's the case, give below script a shot -

*SELECT(%YEAR_SEL%,YEAR,TIME,LEVEL=YEAR)

*SELECT(%MON_SEL%,MONTHNUM,TIME,LEVEL= MONTH)

// Fetch the rates for all members (2013.units, 2014.units, etc.) in variable contracts_2013, contracts_2014 dynamically

*LOOKUP Contracts

   *FOR %LOOP_YEAR% = %YEAR_SEL%

         *DIM CONTRACTS_%LOOP_YEAR%:TIME="%LOOP_YEAR%.UNITS"

    *NEXT

   *DIM ACCOUNT_RS = PRICEM2

    // Add the remaining dimensions of Contracts model here with relevant members

*ENDLOOKUP

//write new records in planning model

*FOR %L_YEAR% = %YEAR_SEL%

     *FOR %L_MONTH% = %MON_SEL%

         *WHEN ACCOUNT_RS = PRICEM2

          *REC(EXPRESSION = LOOKUP(CONTRACTS_%L_YEAR%),                          TIME=%L_YEAR%.%L_MONTH%)

          *ENDWHEN

     *NEXT

*NEXT

Regards,

Ashish

[former_member186338 · Accepted solution]

Hi Adrian,

I am unable to completely understand the logic but I will try to answer the simple question:

You have some list of normal base periods in PLANNING like YYYY.MMM and you want to LOOKUP the value from CONTRACTS from YYYY.UNITS:

I do not recommend using FOR/NEXT loops due to terrible performance!

Please, create a property in the TIME dimension like UNITS and for all normal base periods YYYY.MMM fill it with YYYY.UNITS (corresponding year)

Then:

*LOOKUP Contracts

*DIM UN:TIME=TIME.UNITS

*ENDLOOKUP

Will do the job!

This property is very easy to maintain...

Vadim

[adrian_pmelchor · Accepted solution]

What do yo mean.

Should I put simply

*LOOKUP Contracts

*DIM UN:TIME=TIME.UNITS

*ENDLOOKUP

Or

*LOOKUP Contracts

*FOR %YEAR% = 2012, 2013, 2014,...

*DIM UN:TIME = %YEAR%.UNITS

*NEXT

*ENDLOOKUP

[adrian_pmelchor · Accepted solution]

I've already tried this and it is when I get syntax error.

[former_member186338 · Accepted solution]

If you ALREADY created the property UNITS in TIME dimension and filled it with YYYY.UNIT members, then the code:

*LOOKUP Contracts

*DIM UN:TIME=TIME.UNITS

*ENDLOOKUP

will not generate any error!

Is it clear?

[adrian_pmelchor · Accepted solution]

Awesome.

Simple and correct.

Very thank you Vadim.

[adrian_pmelchor · Accepted solution]

In this case I mean the answer of Ashish Gupta, yours works perfectly.

[former_member186338 · Accepted solution]

In general always try to avoid FOR/NEXT loops even if the alternative is to maintain some property. FOR/NEXT will generate endless lines of code... (it's just text processing, not a real programmatic loop).

Vadim