Using a filter in RUN Allocation

[Former Member]

Does anyone know how to use a filter in the WHAT, WHERE clauses in RUN Allocation?

I need to use something like WHERE=%STAT_ACCTS% AND [GROUP]<>"PL" but it dosen't work.

On the contrary, is there a way to get the basemembers of a parent ID based on a filter?

I could also use something like *XDIM_MEMBERSET ACCOUNT =BAS(STACCTS) AND [GROUP]<>"PL" but XDIM_MEMBERSET does not support conditions.

Thanks in advance for help!

[krishna_priya1 · Accepted solution]

You can first define scope using XDIM_MEMBERSET and then filter with XDIM_FILTER . Below code first considers bas(statccts) for scope and then filters BAS(STACCTS) based on filter GROUP=PL

*XDIM_MEMBERSET ACCOUNT =BAS(STACCTS)
*XDIM_FILTER ACCOUNT = [ACCOUNT].PROPERTIES("GROUP") = "PL"

// code for allocation

Hope this helps .

[Former Member · Accepted solution]

I need to use them together.. for example:

*XDIM_MEMBERSET PRODUCT AS %SKU% = BAS(%PROD%)

*XDIM_FILTER PRODUCT = [PRODUCT].PROPERTIES("LEVEL")="SEG"

*FOR %PRODTOUSE%=%PRODUCT_SET%

*RUNALLOCATION

*FACTOR=1

*DIM PRODUCT WHAT=%PRODTOUSE%; WHERE=%SKU%;

*ENDALLOCATION

*NEXT

In this case, i need to pass a member/variable when specifying WHERE but i only need it to work on bas products which have the LEVEL property set as SEG. If i could set my filter condition as *XDIM_FILTER PRODUCT AS %SKU% = [PRODUCT].PROPERTIES("LEVEL")="SEG", that would help, but it dosen't work.

[former_member200327 · Accepted solution]

It's god that you posted your script. Now we can see that you really don't need that XDIM_FILTER.

You can change you DIM statement to something like

*DIM PRODUCT WHAT=%PRODTOUSE%; WHERE=BAS(%PROD%)

Unfortunately you have two different definitions of PRODUCT scope for WHERE clause.

The other one could be implemented with

*SELECT(%SKU%,ID, PRODUCT,"[CALC] = 'N' AND [LEVEL] = 'SEG'")

Hope this helps,

Gersh

[Former Member · Accepted solution]

Unfortunately what i need is a way to put a filter in the where condition also so it looks like:

*DIM PRODUCT WHAT=%PRODTOUSE%; WHERE=BAS(%PROD%) AND LEVEL="SEG";

But this does not work

[former_member200327 · Accepted solution]

You have to change structure of dimension PRODUCT: instead of using hierarchy node %PROD% add a Property that would select same members as that Node. Than use 2 properties in WHERE.

[Former Member · Accepted solution]

Thanks for the replies... but my problem is still not solved... if either of the following could work that would solve this:

a) have my xdim_memberset set to only those products which meet the condition [LEVEL]="seg" and are the bas members of the product i pick from the hierarchy. For this i would need a select statement which works like:

*SELECT(%SKU%,[ID],"PRODUCT","[PARENTH1]=%PRODUCT_SET% AND [LEVEL]='SEG' ")

OR

b) a way to create a variable using xdim_filter that i can use anywhere else in my code

c) put a condition in the WHERE clause of the allocation script so i could use something like:

DIM PRODUCT WHAT=something; WHERE=BAS(%PRODUCT_SET%) AND [LEVEL]="SEG";

but none of the above options are working