Make *SELECT return unique values

[Former Member]

Hi experts

My SELECT statement returns a list which contains duplicates:

*SELECT(%DEPNACCS%,DEPNACC,AssetClass,Calc=N)

I understand in MS I could remove the duplicates with some SQL:

*SELECT(%DEPNACCS%,DEPNACC,AssetClass,"Calc=N AND DEPNACC in select distinct DEPNACC from AssetClass")

Is there a way to achieve the same thing with NW?

[former_member186338 · Accepted solution]

DEPNACC is a property containing what? Account?

Then

*SELECT(%DEPNACCS%,DEPNACC,AssetClass,Calc=N)

*SELECT(%DEPNACCSUN%,ID,ACCOUNT,ID=%DEPNACCS%)

Vadim

[Former Member · Accepted solution]

Vadim, Yes, DEPNACC is a property contain an Account.

I can't use *SELECT(%DEPNACCSUN%,ID,ACCOUNT,ID=%DEPNACCS%)

Because ID=%DEPNACCS% will not work if %DEPNACCS% has more than one member. In this case it contains 730000,730000,730001,730000.

It seems to be looking for the property to contain the entire string, and it returns nothing.

I tested it without duplicates and it still wouldn't work However, this will work (though is of no use to me):

*SELECT(%DEPNACCSUN%,ID,ACCOUNT,ID=730000,730000,730001,730000)

Seems like (yet another) bug to me.

[former_member200327 · Accepted solution]

What version and SP are you using? What Vadim suggested became possible in OSS Note issued 3-4 years back.

[former_member186338 · Accepted solution]

Agree with Gersh: "Seems like (yet another) bug to me." - you have to provide version info!

[Former Member · Accepted solution]

7.5 NW SP15

[former_member186338 · Accepted solution]

It has to work on SP15, please read http://service.sap.com/sap/support/notes/1747581

Please show the result of UJKT:

*SELECT(%DEPNACCS%,DEPNACC,AssetClass,Calc=N)

*SELECT(%DEPNACCSUN%,ID,ACCOUNT,ID=%DEPNACCS%)

*XDIM_MEMBERSET ACCOUNT=%DEPNACCSUN%

Vadim

[Former Member · Accepted solution]

I can be more precise and provide some more detail:

*SELECT(%DEPNACCS%,DEPN_EXP_ACC,AssetClass,CALC=N AND DEPN_EXP_ACC<>"")

*SELECT(%DEPNACCSUN%,ID,ACCOUNTSKF,ID=%DEPNACCS%)

*XDIM_MEMBERSET ACCOUNTC=%DEPNACCS%

*XDIM_MEMBERSET ACCOUNTC=%DEPNACCSUN%

UJKT result is:

*XDIM_MEMBERSET ACCOUNTC=730000,730000,730000,730002,730003

*XDIM_MEMBERSET ACCOUNTC=

Here's the weird part:

*SELECT(%DEPNACCSUN%,ID,ACCOUNTSKF,ID=730000,730000,730000,730002,730003)

*XDIM_MEMBERSET ACCOUNTC=%DEPNACCSUN%

returns this:

*XDIM_MEMBERSET ACCOUNTC=730000,730002,730003

[former_member186338 · Accepted solution]

Ups, looks like a bug in BPC NW 7.5

I was able to test on BPC NW 7.5 SP 17:

Script:

*SELECT(%TBE%,[PLANBE],TITLES,CALC=N AND PLANBE<>'')

*XDIM_MEMBERSET BE=%TBE%

*SELECT(%TBEUN%,[ID],BE,ID=%TBE%)

*XDIM_MEMBERSET BE=%TBEUN%

LGX:

*XDIM_MEMBERSET BE=BE1000,ELIMBE,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE5000,BE5000,BE5000,BE5000,BE5000,BE5000,BE5000,BE5100,BE5100,BE5200,BE1000

*XDIM_MEMBERSET BE=

Empty!

With BPC NW 10 SP14 (

CPMBPC    800    0014    SAPK-80014INCPMBPC    CPM Business Planning and Consolidation

Same script:

*SELECT(%TBE%,[PLANBE],TITLES,CALC=N AND PLANBE<>'')

*XDIM_MEMBERSET BE=%TBE%

*SELECT(%TBEUN%,[ID],BE,ID=%TBE%)

*XDIM_MEMBERSET BE=%TBEUN%

LGX:

*XDIM_MEMBERSET BE=BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,ELIMBE,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE5000,BE5000,BE5000,BE5000,BE5000,BE5000,BE5100,BE5100,BE5000,BE5200

*XDIM_MEMBERSET BE=BE1000,BE3000,ELIMBE,BE4000,BE5000,BE5100,BE5200

Vadim

[former_member186338 · Accepted solution]

Has to be tested on BPC NW 7.5 SP19 and if there is the same bug - open case with SAP support.

[former_member200327 · Accepted solution]

Can you try replacing 2nd SELECT with

*SELECT(%DEPNACCSUN%,ID,AssetClass, DEPN_EXP_ACC=%DEPNACCS%)

[former_member186338 · Accepted solution]

...ID,AssetClass...??? But what is the idea?

[former_member200327 · Accepted solution]

Idea is to *SELECT on list that came form selection because in that case it removes duplicates from selection list.

-Gersh

[former_member186338 · Accepted solution]

But the syntax provided in your reply will not help! It will fill %DEPNACCSUN% with the ID's of dimension AssetClass, not ID's of ACCOUNTSKF...

[former_member200327 · Accepted solution]

This will be next step if %DEPNACCSUN% holds unique members.

-Gersh

[former_member186338 · Accepted solution]

But the property DEPN_EXP_ACC of unique members will not be unique

Look on the sample:

//Dimension TITLES has property PLANBE containing member of dimension BE

*SELECT(%PROPSNONUN%,[PLANBE],TITLES,CALC=N AND PLANBE<>'')

*XDIM_MEMBERSET BE=%PROPSNONUN%

*SELECT(%IDUN%,[ID],TITLES,CALC=N AND PLANBE=%PROPSNONUN%)

*XDIM_MEMBERSET TITLES=%IDUN%

LGX:

*XDIM_MEMBERSET BE=BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,ELIMBE,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE5000,BE5000,BE5000,BE5000,BE5000,BE5000,BE5100,BE5100,BE5000,BE5200

*XDIM_MEMBERSET TITLES=0400,1100,1101,1102,1103,1104,1105,1106,1107,1108,1109,1110,1111,1112,1113,1114,1117,1120,1121,1122,1123,1131,1132,1133,1134,1135,1136,1138,1139,1140,1141,1142,1143,1144,1145,1146,1147,1148,1149,1150,1151,1152,1153,1154,1155,1156,1157,1158,1159,1160,1161,1162,1163,1164,1165,1166,1167,1168,1169,1170,1171,1172,1173,1174,1175,1176,1177,1178,1190,1192,1193,1195,1196,1200,1209,1210,1211,1212,1213,1214,1215,1216,1217,1218,1219,1220,1221,1222,1223,1224,1225,1232,1233,1234,1235,1244,1250,1251,1252,1253,1254,1290,1291,1292,1300,1301,1309,1342,1346,1347,1392,1393,1400,1500,1610,1632,1633,1640,1644,1647,1666,1690,1695,1901,1902,1903,1AAA,1BBB,1CCC,1DDD,1ELIM,3100,3101,3102,3103,3104,3105,3106,3107,3108,3109,3110,3115,3120,3121,3122,3123,3125,3130,3131,3132,3140,3141,3142,3143,3150,3151,3152,3153,3160,3161,3170,3171,3172,3200,3220,3230,3240,3250,3260,3270,3300,3330,3340,3370,3400,3540,3600,3630,3640,3650,3660,3666,3670,4100,4101,4102,4103,4104,4105,4106,4111,4112,4113,4114,4115,4120,4121,4122,4130,4131,4132,4133,4134,4135,4136,4137,4150,4200,4234,4250,4270,4300,4301,4350,4400,4600,5101,5102,5103,5104,5201,5202,5251,5252,5400,5800

We need to have unique list of BE members containing in PLANBE property...

In BPC 10 it's working as expected:

*SELECT(%PROPSNONUN%,[PLANBE],TITLES,CALC=N AND PLANBE<>'')

*XDIM_MEMBERSET BE=%PROPSNONUN%

*SELECT(%IDUN%,[ID],BE,ID=%PROPSNONUN%)

*XDIM_MEMBERSET BE=%IDUN%

LGX:

*XDIM_MEMBERSET BE=BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,BE1000,ELIMBE,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE3000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE4000,BE5000,BE5000,BE5000,BE5000,BE5000,BE5000,BE5100,BE5100,BE5000,BE5200

*XDIM_MEMBERSET BE=BE1000,BE3000,ELIMBE,BE4000,BE5000,BE5100,BE5200

In BPC 7.5 SP17 (the latest I have) the second SELECT "*SELECT(%IDUN%,[ID],BE,ID=%PROPSNONUN%)"  return nothing. It's a bug!

Vadim

[Former Member · Accepted solution]

Vadim's right. If it worked with SP15 it would return  list of IDs from AssetClass. Although that list would be unique, the DEPN_EXP_ACC properties of those members would not be unique.

I noticed another strange thing though: the AssetClass dimension is used by the source application. When I ran Gersh's suggestion it read the first member for %DEPNACCS% but no more.

However, AccountSKF is not used in the source application. When I try to get unique IDs from it here:

*SELECT(%DEPNACCSUN%,ID,AccountSKF, ID=%DEPNACCS%)

...it's not recognising even the first member

But a bug's a bug I guess. Better to concentrate my efforts on moving to SP19. Thanks to you both for your help, much appreciated.