Wrong values in FOR/NEXT Loop

[former_member603658]

I have written following script  sales distribution planning model here i am trying to LOOKUP SALES QUANTITY from the Sales Planning model Region and Product wise, then i LOOKUP the Capacity of container from the SALES DISTRIBUTION DRIVER model and after performing the calculation trying to write in the SALES DISTRIBUTION MODEL WAREHOUSE AND PRODUCT WISE (WAREHOUSE = REGION)

My script is as follows

*XDIM_MEMBERSET ENTITY = %ENTITY_SET%

*XDIM_MEMBERSET VERSION =%VERSION_SET%

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET DISTRIBUTORS = "NO_DIST"

*XDIM_MEMBERSET COST_CENTER = "1010101001"

*XDIM_MEMBERSET DIST_METHOD = "DEDICATED"

*XDIM_MEMBERSET ACC_SALESDIST = "NO_TRIP"

*LOOKUP SALES//the real name of model 1

*FOR %REG% = 1003, 1002

*FOR %PROD% = 0100100101

*DIM VERSION = "Plan"   

*DIM ENTITY = "1000"

*DIM REGION = %REG%

*DIM PRODUCT = %PROD%

*DIM TIME = %TIME_SET%

*DIM Q%REG%%PROD%: ACC_SAL_M ="QTY"

*NEXT

*NEXT

*ENDLOOKUP

*LOOKUP SALES_DIST_DRIVER//the real name of model 2

*DIM VERSION = "Plan"

*DIM ENTITY = %ENTITY_SET%

*DIM TIME = %TIME_SET%

*DIM VEHICLE = "50FT"

*DIM DISTRIBUTORS = "NO_DIST"

*DIM WAREHOUSE = "NOWH"

*DIM CAPACITY: ACC_SDIST_DRIVER ="CAPACITY"

*ENDLOOKUP

*WHEN_REF_DATA = MASTER_DATA

*FOR %REG% = 1003, 1002

*FOR %PROD% = 0100100101

*WHEN ACC_SALESDIST

*IS "NO_TRIP"

*WHEN DIST_METHOD

*IS "DEDICATED"

*WHEN DISTRIBUTORS

*IS "NO_DIST"

*WHEN COST_CENTER

*IS "1010101001"

*WHEN PRODUCT

*IS %PROD%

*WHEN WAREHOUSE

*IS %REG%

*REC(EXPRESSION = LOOKUP(Q%REG%%PROD%) / LOOKUP(CAPACITY))

*ENDWHEN

*ENDWHEN

*ENDWHEN

*ENDWHEN

*ENDWHEN

*ENDWHEN

*COMMIT

*NEXT

*NEXT

I am getting following results, system is only picking the values of WAREHOUSE (WAREHOUSE = REGION) 1003 only(i.e. the first value pickup from lookup variable) and writing in both WAREHOUSE 1003 AND 1002. My concern is that it should write REGION AND PRODUCT wise date in respective region and product rather repeating the same data in the other.

LGX:

*XDIM_MEMBERSET ENTITY = 1000

*XDIM_MEMBERSET VERSION =Plan

*XDIM_MEMBERSET TIME = 2016.01

*XDIM_MEMBERSET DISTRIBUTORS = "NO_DIST"

*XDIM_MEMBERSET COST_CENTER = "1010101001"

*XDIM_MEMBERSET DIST_METHOD = "DEDICATED"

*XDIM_MEMBERSET ACC_SALESDIST = "NO_TRIP"

*LOOKUP SALES

*DIM VERSION = "Plan"

*DIM ENTITY = "1000"

*DIM REGION = 1003

*DIM PRODUCT = 0100100101

*DIM TIME = 2016.01

*DIM Q10030100100101: ACC_SAL_M ="QTY"

*DIM VERSION = "Plan"

*DIM ENTITY = "1000"

*DIM REGION = 1002

*DIM PRODUCT = 0100100101

*DIM TIME = 2016.01

*DIM Q10020100100101: ACC_SAL_M ="QTY"

*ENDLOOKUP

*LOOKUP SALES_DIST_DRIVER

*DIM VERSION = "Plan"

*DIM ENTITY = 1000

*DIM TIME = 2016.01

*DIM VEHICLE = "50FT"

*DIM DISTRIBUTORS = "NO_DIST"

*DIM WAREHOUSE = "NOWH"

*DIM CAPACITY: ACC_SDIST_DRIVER ="CAPACITY"

*ENDLOOKUP

*WHEN_REF_DATA = MASTER_DATA

*WHEN ACC_SALESDIST

*IS NO_TRIP

*WHEN DIST_METHOD

*IS DEDICATED

*WHEN DISTRIBUTORS

*IS NO_DIST

*WHEN COST_CENTER

*IS 1010101001

*WHEN PRODUCT

*IS 0100100101

*WHEN WAREHOUSE

*IS 1003

*REC(EXPRESSION = LOOKUP(Q10030100100101) / LOOKUP(CAPACITY))

*ENDWHEN

*ENDWHEN

*ENDWHEN

*ENDWHEN

*ENDWHEN

*ENDWHEN

*COMMIT

*WHEN ACC_SALESDIST

*IS NO_TRIP

*WHEN DIST_METHOD

*IS DEDICATED

*WHEN DISTRIBUTORS

*IS NO_DIST

*WHEN COST_CENTER

*IS 1010101001

*WHEN PRODUCT

*IS 0100100101

*WHEN WAREHOUSE

*IS 1002

*REC(EXPRESSION = LOOKUP(Q10020100100101) / LOOKUP(CAPACITY))

*ENDWHEN

*ENDWHEN

*ENDWHEN

*ENDWHEN

*ENDWHEN

*ENDWHEN

*COMMIT

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

LOG:

LOG BEGIN TIME:2015-11-03 06:09:06

FILE:\ROOT\WEBFOLDERS\PLANNING \ADMINAPP\SALES_DISTRIBUTION\TEST.LGF

USER:ABC

APPSET:PLANNING

APPLICATION:SALES_DISTRIBUTION

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

ACC_SALESDIST,COST_CENTER,DISTRIBUTORS,DIST_METHOD,ENTITY,MEASURES,PRODUCT,TIME,VERSION,WAREHOUSE

#dim_memberset=7

ENTITY:1000,1 in total.

VERSION:Plan,1 in total.

TIME:2016.01,1 in total.

DISTRIBUTORS:NO_DIST,1 in total.

COST_CENTER:1010101001,1 in total.

DIST_METHOD:DEDICATED,1 in total.

ACC_SALESDIST:NO_TRIP,1 in total.

REC :LOOKUP(Q10030100100101) / LOOKUP(CAPACITY)

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 0.00 ms. 1  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY LOOKUP DATA FROM APPLICATION: SALES

QUERY TIME : 0.00 ms. 2  RECORDS QUERIED OUT.

QUERY LOOKUP DATA FROM APPLICATION: SALES_DIST_DRIVER

QUERY TIME : 0.00 ms. 1  RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :0.00 ms.

1  RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 1  RECORDS ARE GENERATED.

DATA TO WRITE BACK:

ACC_SALESDIST COST_CENTER DISTRIBUTORS DIST_METHOD ENTITY PRODUCT TIME VERSION WAREHOUSE SIGNEDDATA

NO_TRIP 1010101001 NO_DIST DEDICATED 1000 0100100101 2016.01 Plan 1003 - 3.33

Result should write 16.7 value in WAREHOUSE 1002 and PRODUCT 01

I am working on SAP BPC 10.1 Patch 7.0

Please suggest solution

Regards

[former_member186338 · Accepted solution]

Sorry but the script is not correct and have to be rewritten completely. Please explain the requirements in details (not script!) -

Hint - it's better to push data using DESTINATION_APP from SALES model using lookup to SALES_DIST_DRIVER for CAPACITY into SALES_DISTRIBUTION_MODEL

Vadim

[former_member603658 · Accepted solution]

Hi

Thanks Vadim for the prompt response, actually there are two challenges in my requirement

ONE I want to lookup sales QTY from sales model on the basis of two variables i.e. REGION and PRODUCT.

TWO After performing calculation on the queried data, want to save the data in SALES DISTRIBUTION model on the basis of two VARIABLES i.e. WAREHOUSE and PRODUCT.

Please note WAREHOUSE = REGION.

I just want to understand how can I write data considering the two variable. This writing must be performed on the respective dimension member only and must not be repeated on other members.

I belief that if I use the DESTINATION_APP statement, the above requirement will remain same.

Please correct me if I am wrong and suggest the solution.

[former_member186338 · Accepted solution]

Please, provide the required info! Including all dimensions of all models!

What is wrong in the script:

You don't need to use FOR/NEXT

You don't need to use variables

LOOKUP will be different

Data will be pushed

No *WHEN_REF_DATA = MASTER_DATA

No COMMIT

Etc...

Vadim

[former_member603658 · Accepted solution]

Thanks for response, please note the below details

MODELS

SALES MODEL

• ACCOUNT :ACC_SAL_M
• TIME (2016.01)
• PRODUCT(0100100101)
• VERSION (CATEGORY)
• REGION (1002, 1003, 1004)
• ENTITY (1000, 2000)

SALES DISTRIBUTION MAIN

• ACC_SALESDIST (NO_TRIP)
• DIST_METHOD (DEDICATED, SEMI DEDICATED ETC)
• DISTRIBUTORS (no_dist))
• COST_CENTER (1010101001)
• PRODUCT (0100100101)
• WAREHOUSE (1002, 1003, 1004) WAREHOUSE = REGION
• TIME (2016.01)
• VERSION (Plan )
• ENTITY

SALES DISTRIBUTION DRIVER

• VERSION (Plan)
• ENTITY
• TIME
• VEHICLE = "50FT"
• DISTRIBUTORS
• WAREHOUSE
• ACC_SDIST_DRIVER ="CAPACITY"

REQUIREMENT

Get the region (warehouse) and product wise SALES QTY from sales model and divide it with the capacity of 50FT Container from sales distribution driver model and then write it to the SALES DISTRIBUTION Model warehouse and product wise.

EXAMPLE

ACCESS THE SALES DIMENSION AND PICK THE SALES QTY OF A PARTICULAR PRODUCT (0100100101) FOR PARTICULAR REGION (1002, 1003) , then divided this value with CAPACITY  from SALES DISTRIBUTION DRIVER model and post the result on SALES DISTRIBUTION Model WAREHOUSE and PRODUCT wise. Data of every region and product must be written on once and must be repeated.

SAP_BW 740       0012     SAPKW74012      SAP Business Warehouse

CPMBPC 810       0007     SAPK-81007INCPMBPC     SAP Business Planning and Consolidation

  We want to execute this script as DM Package and I am checking the script on UJKT

[former_member186338 · Accepted solution]

Script:

//Script in SALES!

*LOOKUP SALES_DIST_DRIVER

*DIM VEHICLE = "50FT"

*DIM DISTRIBUTORS = "NO_DIST"

*DIM WAREHOUSE = "NOWH"

*DIM CAPACITY: ACC_SDIST_DRIVER ="CAPACITY"

//TIME,ENTITY,VERSION will be taken from the current record in WHEN/ENDWHEN

*ENDLOOKUP

*XDIM_MEMBERSET ENTITY=%ENTITY_SET% //or 1000 for test

*XDIM_MEMBERSET VERSION=Plan

*XDIM_MEMBERSET TIME=%TIME_SET% //or 2016.01 for test

*XDIM_MEMBERSET ACCOUNT=ACC_SAL_M

*XDIM_MEMBERSET REGION=1003,1002

*XDIM_MEMBERSET PRODUCT=0100100101 //Can be a list of products...

*DESTINATION_APP SALES_DISTRIBUTION_MAIN

*SKIP_DIM = ACCOUNT

*RENAME_DIM REGION=WAREHOUSE

*ADD_DIM DIST_METHOD=DEDICATED, ACC_SALESDIST=NO_TRIP, DISTRIBUTORS=NO_DIST, COST_CENTER=1010101001

*WHEN TIME

*IS *

*REC(EXPRESSION = %VALUE%/LOOKUP(CAPACITY))

*ENDWHEN

[former_member603658 · Accepted solution]

Thanks for the such an efficient script Vadim, it perfectly working however i have modified a bit by adding FOR NEXT on destination_app otherwise it was only picking the first value .

*FOR %REG% = 1003, 1002

*FOR %PROD% = 0100100101, 0100100102

*XDIM_MEMBERSET ENTITY= "1000"

*XDIM_MEMBERSET VERSION=Plan

*XDIM_MEMBERSET TIME= "2016.01"

*XDIM_MEMBERSET ACC_SAL_M="QTY"

*XDIM_MEMBERSET REGION= BAS(%REG%)

*XDIM_MEMBERSET PRODUCT=%PROD%

*DESTINATION_APP=SALES_DISTRIBUTION

*SKIP_DIM = ACC_SAL_M, REGION

//*RENAME_DIM REGION=WAREHOUSE

*ADD_DIM DIST_METHOD=DEDICATED, ACC_SALESDIST=NO_TRIP, DISTRIBUTORS=NO_DIST, COST_CENTER=1010101001, WAREHOUSE = %REG%

*WHEN TIME

*IS *

*REC(EXPRESSION = %VALUE%/LOOKUP(CAPACITY))

//*REC(EXPRESSION = %VALUE%)

*ENDWHEN

*NEXT

*NEXT

Thanks for the great response.

[former_member186338 · Accepted solution]

Sorry, but still incorrect!

The key point is here (not described before):

*XDIM_MEMBERSET REGION= BAS(%REG%)

In SALES model 1003, 1002 are parents and in the target SALES_DISTRIBUTION 1003, 1002 are base members!

In this case you will need a single FOR/NEXT loop: *FOR %REG% = 1003, 1002, but you don't need FOR/NEXT for products!

Result:

*LOOKUP SALES_DIST_DRIVER

*DIM VEHICLE = "50FT"

*DIM DISTRIBUTORS = "NO_DIST"

*DIM WAREHOUSE = "NOWH"

*DIM CAPACITY: ACC_SDIST_DRIVER ="CAPACITY"

*ENDLOOKUP

*XDIM_MEMBERSET ENTITY=1000

*XDIM_MEMBERSET VERSION=Plan

*XDIM_MEMBERSET TIME=2016.01

*XDIM_MEMBERSET ACC_SAL_M=QTY

*XDIM_MEMBERSET PRODUCT=0100100101, 0100100102

*FOR %REG% = 1003, 1002

*XDIM_MEMBERSET REGION=BAS(%REG%)

*DESTINATION_APP SALES_DISTRIBUTION_MAIN

*SKIP_DIM = ACC_SAL_M, REGION

*ADD_DIM DIST_METHOD=DEDICATED, ACC_SALESDIST=NO_TRIP, DISTRIBUTORS=NO_DIST, COST_CENTER=1010101001, WAREHOUSE = %REG%

*WHEN TIME

*IS *

*REC(EXPRESSION = %VALUE%/LOOKUP(CAPACITY))

*ENDWHEN

*NEXT

FOR/NEXT loops are not real loops - just text processing as you can see in lgx result. And you have to avoid FOR/NEXT loops as much as possible - very slow!

Even in this case if you maintain some property in REGION dimension (WAREHOUSE) with target WAREHOUSE member for all base members you can avoid FOR/NEXT at all. The question is - how many WAREHOUSE members you will have?

Vadim

[former_member186338 · Accepted solution]

P.S. Also, to improve calculations accuracy I can recommend to create in REGION dimension additional base members like:

1003_NODE, 1002_NODE etc...

And aggregate all base members under each node 1003, 1002... into 1003_NODE, 1002_NODE... with additional loop before division by LOOKUP(CAPACITY)

Then perform division with DESTINATION_APP using property WAREHOUSE filled for each 1003_NODE, 1002_NODE...:

ID:                     1003_NODE 1002_NODE

WAREHOUSE: 1003              1002

*ADD_DIM ... WAREHOUSE=REGION:WAREHOUSE

[former_member603658 · Accepted solution]

Thanks Vadim for the correction .... now its perfectly working