select statement to exclude the members in script

[BalaValluru]

Hi Experts

Need to exclude few costcenter members in Parenth2(only BA00).

I have huge cc members to exclude (BA00). Is there any way to exclude the members with logic Can some one please help me in select statement.

*XDIM_MEMBERSET COSTCENTER AS %TL%= BAS(TOTAL)
*SELECT(%B00%,"[ID]", COSTCENTER, "[ID] = %TL% AND <> [PARENTH2]=B00'")
*XDIM_MEMBERSET COSTCENTER =%B00% 

Thanks

KishoreV

[former_member186338 · Accepted solution]

You can use the following:

*XDIM_MEMBERSET ACCOUNT AS %A1%=BAS(PL600)
*XDIM_MEMBERSET ACCOUNT = %A1%
*XDIM_MEMBERSET ACCOUNT AS %A2%=BAS(PL200)
*XDIM_MEMBERSET ACCOUNT = %A2%
*SELECT(%A3%,ID,ACCOUNT,ID=%A1% AND ID<>%A2%)
*XDIM_MEMBERSET ACCOUNT = %A3%

[former_member186338 · Accepted solution]

P.S. Result in UJKT:

LGX:

*XDIM_MEMBERSET ACCOUNT = PL010,PL020,PL110,PL120,PL310,PL320,PL330,PL510,PL999
*XDIM_MEMBERSET ACCOUNT = PL110,PL120
*XDIM_MEMBERSET ACCOUNT = PL010,PL020,PL310,PL320,PL330,PL510,PL999

[BalaValluru · Accepted solution]

vadim.kalinin

Thank you so much for your inputs! . I will test and update.

Thanks

Kishore V

[former_member186338 · Accepted solution]

valluru

Already tested 🙂

In your case the code will be:

*XDIM_MEMBERSET COSTCENTER AS %A1%=BAS(TOTAL)
*XDIM_MEMBERSET COSTCENTER = %A1% //just for test
*XDIM_MEMBERSET COSTCENTER AS %A2%=BAS(BA00)
*XDIM_MEMBERSET COSTCENTER = %A2% //just for test
*SELECT(%A3%,ID,COSTCENTER,ID=%A1% AND ID<>%A2%)
*XDIM_MEMBERSET COSTCENTER = %A3% //result of select

[BalaValluru]

Hi Experts

Created property in CC to exclude. can some one correct below SL.

*XDIM_MEMBERSET COSTCENTER AS %CC%= BAS(TOTAL)
*SELECT(%B00%,[ID],COSTCENTER,[BA]<>BA00)
*XDIM_MEMBERSET COSTCENTER =%B00%

[former_member186338]

Property is not the best solution - hard to maintain. Look on my answer with 2 "AS"

[BalaValluru]

sreeraj.sukumaran

When I tested in UJKT : say's invalid select statement.

Seems select statement not recognizing the parent hieararcy.

Is there any other statement which can work for parent group exclude.

Thanks for help

Kishore V

[sreeraj_sukumaran]

*XDIM_MEMBERSET COSTCENTER AS%TL%= BAS(TOTAL)

*SELECT(%B00%,"[ID]", COSTCENTER, "[ID] = %TL% AND [PARENTH2] <> BA00'")
*XDIM_MEMBERSET COSTCENTER =%B00%