Script Logic. Variables in Allocation to fix dimension property

[Former Member]

Hello BPC Experts,

We are trying to execute the following allocation.

When we do the validate and save for the logic we get the following error:

Validation status of executable file: Failed - Invalid column name 'AUX_VRI'. in:select [ID] from mbrPRODUCTO where [REPARTOFIN]=AUX_VRI

In the dimension we have a member whit ID AUX_VRI.

It seems that there is problem in the where part when we pass a variable. It is possible to pass a variable to get the name of the property??

*RUNALLOCATION       

          *DIM PRODUCTO       WHAT=%AUX_REPARTO%;     WHERE=[REPARTOFIN]=%AUX_REPARTO%;

*ENDALLOCATION

*COMMIT

The idea that we have in mind is to copy the values that are stored in a member whose value is stored in the variable %AUX_REPARTO% into all the members that have the same value in the property [REPARTOFIN].

For instance we have the value 5 in the member ID PRODUCTA and we want to copy this value in the members PRODUCT1, PRODUCT2 and PRODUCT3 that have the following value for the property [REPARTOFIN]=PRODUCTA .

Is there any other workaround to do that??

Thanks in advance

[former_member186498 · Accepted solution]

Hi Lauro,

try using a select statement

*SELECT(%Prods%,"ID","PRODUCTO","REPARTOFIN = %AUX_REPARTO%") and a FOR/NEXT statement with the %Prods% around the runallocation.

Kind regards

     Roberto

[Former Member · Accepted solution]

Hi Roberto,

I have tryed your idea but the variables are still not working properly with your solution.

As I am not working with too many items I have hardcoded the possible combinations avoiding the use of vairables in the second part of the ALLOCATION.

It is not the most elegant solution but it is working for the moment.

Best regards

[former_member186498 · Accepted solution]

Hi Lauro,

if you post all the script and explain what you're trying to obtain maybe you will receive an help to improve your script.

Kind regards

     Roberto

[Former Member · Accepted solution]

Hello Roberto,

We have the following situation:

Dimension PRODUCTO

ID                             REPARTOFIN

Product1                   AUX_REPARTO   

Product1a                 Product1

Product1b                 Product1

Product1c                 Product1

Product2                   AUX_REPARTO

Product2a                 Product2 

Prodcut2b                 Product2

We want to copy the values in Product1 in Product1a, Product1b and Product1c. And the same for Product2 in Product2a and Product2b.  We had the following idea, but the variable in the where part of the allocation is not working properly and give us a compilation error

*SELECT(%AUX_REPARTO%,"[ID]",PRODUCTO,"[REPARTOFIN]='AUX_REPARTO'")

*XDIM_MEMBERSET PRODUCTO=%AUX_REPARTO%

*RUNALLOCATION

    *DIM PRODUCTO  WHAT=%AUX_REPARTO%;     WHERE=[REPARTOFIN]=%AUX_REPARTO%;

*ENDALLOCATION

*COMMIT

*XDIM_MEMBERSET PRODUCTO=AUX_PIAS

*RUNALLOCATION       

        *DIM PRODUCTO  WHAT=AUX_PIAS;     WHERE=[REPARTOFIN]='AUX_PIAS';

*ENDALLOCATION

*COMMIT

Is there any possible workaround?

Thanks

[former_member186498 · Accepted solution]

Hi Lauro,

try this

*SELECT(%AUX_REPARTO%,"ID","PRODUCTO","REPARTOFIN='AUX_REPARTO'")

*XDIM_MEMBERSET PRODUCTO=%AUX_REPARTO%

*FOR %REPARTO%=%AUX_REPARTO%

  *RUNALLOCATION

    *DIM PRODUCTO  WHAT='%REPARTO%';     WHERE=[REPARTOFIN]='%REPARTO%';

  *ENDALLOCATION

*NEXT

*COMMIT

Sorry but I don't understand the second parte of your script

*XDIM_MEMBERSET PRODUCTO=AUX_PIAS

*RUNALLOCATION       

        *DIM PRODUCTO  WHAT=AUX_PIAS;     WHERE=[REPARTOFIN]='AUX_PIAS';

*ENDALLOCATION

*COMMIT

if you want just change automatically the value of REPARTOFIN you can use a PROMPT asking for this value, or passing it as parameter when you call your script from another script (then you must refers to it as %P1% inside the script).

Consequently you must change the SELECT statement substituting the fixed value with the corresponding variable.

Kind regards

     Roberto

[Former Member · Accepted solution]

Hi Roberto,

Thanks again for your answer.

When we try to validate & save we get the following error:

Validation status of executable file: Failed - Incorrect syntax near 'AUX_VRI'. in:Select ISBASEMEM From mbrPRODUCTO where [id]=''Product1''

It seems that the system it is not able to solve the value of the where part of the allocation.

*FOR %REPARTO%=%AUX_REPARTO%  

  *RUNALLOCATION  *DIM PRODUCTO  WHAT='%REPARTO%'; WHERE=[REPARTOFIN]='%REPARTO%';  

*ENDALLOCATION *NEXT

The second part of the script shows how we have made it work by hardcoding the values. It has no other meaning.

Best regards

[former_member186498 · Accepted solution]

Hi Lauro,

which version and sp of BPC are you using?

Have you try to debugg the logic to see wich values are in the variables?

Kind regards

     Roberto

[Former Member · Accepted solution]

Hello Roberto,

I have checked my script again and you were right, your syntax is working.

You did the magic again.

Thank you very much!!