ALLOCATION error

[former_member586052]

Hi,

I'm writting an allocation in which I declare a variable (%LIST_CD1%) in a SELECT statement.

Then I use the list to define my CD1 members.

In my CD2 dimension, I want the script to use the corresponding members using the SEG property (which is filled in with the members of CD1).

The Script:

*SELECT(%LIST_CD1%,"ID","CD1","ALLOC='Y'")

*XDIM_MEMBERSET TIME=%TIME_SET%

*XDIM_MEMBERSET C_CATEGORY=%C_CATEGORY_SET%

*RUNALLOCATION

*FACTOR=-USING

*DIM C_ACCT WHAT=BAS(5300000T); WHERE=5300000A; USING=5300000A

*DIM C_DATASRC WHAT=INPUT_MAN; WHERE=ALLOCATED; USING=ALLOCKEY

*DIM CD1 WHAT=%LIST_CD1%; WHERE=BAS(S1701); USING=BAS(S1701)

*DIM CD2 WHAT=<ALL>; WHERE=<ALL>; USING=[SEG]=%LIST_CD1%

*DIM ENTITY WHAT=<ALL>; WHERE=<ALL>; USING=1000

*DIM INTCO WHAT=<ALL>; WHERE=<ALL>; USING=Non_InterCo

*DIM RPTCURRENCY WHAT=LC; WHERE=LC; USING=LC

*DIM CD3 WHAT=NONE; WHERE=<<<; USING=<<<

*DIM CONSOSCOPE WHAT=G_NONE; WHERE=<<<; USING=<<<

*ENDALLOCATION

*COMMIT

When I validate and save the script, it returns the following error message:

Application: CMR

Logic file: Allocation.LGF

Validation status of executable file: Failed

- Invalid column name 'S17090101'. in:select [ID] from mbrCD2 where [SEG]=S17090101

Validation status of syntax: Success

Rem: 'S17090101' is the first member of the list generated by the SELECT statement.

Any idea on how to solve that ?

Thanks in advance for your help.

Yoann

[krishna_priya1 · Accepted solution]

If %LIST_CD1% has only one value , allocation works. Definitely , %LIST_CD1% has multiple values , so its giving error .

I believe ,its better to get %LIST_CD2% using select statement as the way %LIST_CD1% is retrieved. i.e.

*SELECT(%LIST_CD1%,"ID","CD1","ALLOC='Y'")

*SELECT(%LIST_CD2%,"ID","CD2","SEGFLAG='Y'") .

If the first statement returns S1,S2,S3,S4, then in CD2, maintain SEGFLAG property value as 'Y' for the members that satisfy condition SEG= S1,S2,S3,S4 .

Hope this helps .

[JohnL]

Hi Yoann,

pkrishnas is right. You can only pass one value to the in your USING parameter.

Can you see if this works. You may need to modify the syntax by carefully reviewing the log file.

*DIM CD2 WHAT=<ALL>; WHERE=<ALL>; 
USING=[SEG] IN (SELECT ID FROM mbrCD1 WHERE ALLOC = 'Y')

From the help:

The name of the property may need to be enclosed in [brackets] and the values must be enclosed in either double quotes or single quotes. The expression can be written with any SQL-supported syntax, as it will be passed as-is to the SQL query engine

Thanks,

John

[former_member586052]

Hi John, pkrishnas,

Thanks for your very helpfull answers.

It is better now but the problem is that the USING is taking the keys from all my CD2 members... Whereas I need the system to pass it member per member.

So the end result that I'm aiming for is the following:

For each member of the of the CD1 dimension, I need the system to take the corresponding member from my CD2 dimension.

Here is a little example:

CD1 members are AB, BB and CB

CD2 members are I_AB, I_BB, I_CB

So when allocation on CD1 member "AB" is processed, I need the system to only take the CD2 member "I_AB" in the USING.

In the same way, when allocation on CD1 member "BB" is processed, I need the system to only take the CD2 member "I_BB" in the USING.

Etc.

For now, the system is taking I_AB + I_BB + I_CB...

Thanks for helping on that

Yoann

[JohnL]

Hi Yoann,

What you describe is not supported.

You will need to build individual allocations for each CD1 /CD2 combination.

You can use a *FOR loop for that.

Syntax:

*FOR  {variable1} = {set1}  AND {variable2}={set2}

{allocation code goes here}

*DIM CD1 WHAT=variable1; WHERE=BAS(S1701); USING=BAS(S1701)
*DIM CD2 WHAT=<ALL>; WHERE=<ALL>; USING=variable2
etc.

*NEXT

You need to build and using *SELECT statements.

HTH,

John

[krishna_priya1]

Just try below code and see if this wroks.

*FOR %CD1_MEM% = %LIST_CD1%

*RUNALLOCATION
*FACTOR=-USING
...
*DIM CD1 WHAT=%CD1_MEM%; WHERE=BAS(S1701); USING=BAS(S1701)
*DIM CD2 WHAT=<ALL>; WHERE=<ALL>; USING=I_%CD1_MEM%
...
*ENDALLOCATION

*NEXT