Select Member from Dimension A based on Property of Another Dimension

[Eyal_Feiler]

Hi Experts

I would like to run a script from a DM package where we clear the data for a dimension member selection based on a variable for another, before running a calculation.

Example: Clear data for selection where INTERCO = I_1100 based on selection entered of ENTITY = 1100 (All interco are built as: I_[ENTITY VALUE]).

Tried 2 different ways, A) Select the Entity property of Interco. B) Concatenate with a loop.

Neither worked.

First resulted in value but it’s not correct.

Second was a long shot…

Any ideas?

Thanks in advance

Eyal

Method1

Tried loop with concatenate – but that also did not generate.

*XDIM_MEMBERSET CATEGORY = ADDINFO

*XDIM_MEMBERSET TIME = %TIME_SET%

*XDIM_MEMBERSET SCOPE = S_NONE

*XDIM_MEMBERSET CURRENCY = USD

*XDIM_MEMBERSET FLOW = F10

*FOR INTERCO = I_%ENTITY_SET%

*WHEN AUDITID

*IS INPUT3

*REC(FACTOR=1)

*NEXT

*ENDWHEN

*COMMIT

LOG BEGIN TIME:2017-01-18 14:42:52

FILE:\ROOT\WEBFOLDERS\MAI_CONSO\ADMINAPP\Consolidation\CLEARSWAP.LGF

USER:QAEYALFE

APPSET:MAI_CONSO

APPLICATION:Consolidation

[INFO] GET_DIM_LIST(): I_APPL_ID="Consolidation", #dimensions=18

ACCOUNT,ACTIVE_INGREDIEN,ACTIVITY,AUDITID,BANK,CATEGORY,COST_CENTER,COUNTRY,CURRENCY,ENTITY,FLOW,INTERCO,MEASURES,PRODUCTION_LINE,SCOPE,SUBGLOBALRGN,TC,TIME

#dim_memberset=6

ENTITY:1100,1 in total.

CATEGORY:ADDINFO,1 in total.

TIME:2017.03,1 in total.

SCOPE:S_NONE,1 in total.

CURRENCY:USD,1 in total.

FLOW:F10,1 in total.

REC :%value%*(1)

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 3.00 ms. 1 RECORDS QUERIED OUT.

QUERY REFERENCE DATA

CALCULATION TIME IN TOTAL :0.00 ms.

1 RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 1 RECORDS ARE GENERATED.

DATA TO WRITE BACK:

ACCOUNT ACTIVE_INGREDIEN ACTIVITY AUDITID BANK CATEGORY COST_CENTER COUNTRY CURRENCY ENTITY FLOW INTERCO PRODUCTION_LINE SCOPE SUBGLOBALRGN TC TIME SIGNEDDATA

711640 AI_0 AC_20 INPUT3 BA_NONE ADDINFO CC_NONE CO_26 USD 1100 F10 I_1250 PL_NONE S_NONE GR_1003 TC_ILS 2017.03 0.00

1 RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :1.00 ms.

SCRIPT RUNNING TIME IN TOTAL:4.00 s.

LOG END TIME:2017-01-18 14:42:56

File path: \ROOT\WEBFOLDERS\MAI_CONSO\Consolidation\PRIVATEPUBLICATIONS\QAEYALFE\tempfiles\20170118144252\00O2THEGP0O3XFJDNRBJZ3O36_CLEARSWAP.LOG

Method 2

: %ENTITY_SET% is from the DM package, try to select the ENTITY property of INTERCO containing the correct value

// *SELECT(%INTERCO_LIST%,ID,INTERCO,[ENTITY] =%ENTITY_SET%)

// *XDIM_MEMBERSET CATEGORY = ADDINFO

// *XDIM_MEMBERSET INTERCO = %INTERCO_LIST%

// *XDIM_MEMBERSET TIME = %TIME_SET%

// *WHEN AUDITID

// *IS INPUT3

// *REC(FACTOR=0)

// *ENDWHEN

// *COMMIT

Result: Error RUN_LOGIC:No value assigned to dimension "INTERCO"

Dimension Values:
ENTITY ENTITY.INTCO

1100 I_1100

[former_member186338 · Accepted solution]

To be able to process multiple ENTITY selection the following code has to be used:

*SELECT(%IC%,[ID],INTERCO,[ENTITY]=%ENTITY_SET%)
*XDIM_MEMBERSET INTERCO=%IC%
*XDIM_MEMBERSET ENTITY=%ENTITY_SET%
*WHEN ENTITY
*IS INTERCO.ENTITY
*REC(EXPRESSION=0)
*ENDWHEN

Only records with ENTITY=INTERCO.ENTITY will be cleared!

Result (%ENTITY_SET%=DE,FR,BR):

[Eyal_Feiler]

Hi Vadim,

Thank you again. We changed to selection approach to include all, so I saved and will use this approach another time.

Eyal

[Former Member]

Do you have Entity property maintained in Interco dimension as well??? or only with Entity dimension? Because you have shown Entity dimension with Intco property but in select statement used the reverse.....

JP

[former_member186338]

The second approach is correct! Do you correctly pass the value in %ENTITY_SET%?

Please provide the advanced DM script!

Can you also show the log in UJKT?

[former_member186338]

P.S. Do you select a single ENTITY or multiple?