LOOKUP not retrieving data

[arun_varghese]

Hi All,

SAP BPC 10.0

Script Logic:

*XDIM_MEMBERSET P_ACCOUNT = C_NONE

*XDIM_MEMBERSET AUDITTRAIL = PLAN_INPUT

*XDIM_MEMBERSET CATEGORY = PLAN,ACTUAL

*XDIM_MEMBERSET CONT_COUNTRY = US

*XDIM_MEMBERSET COST_CENTER = CC_NONE

*XDIM_MEMBERSET TIME = 2016.08

*XDIM_MEMBERSET ENTITY = E_NONE

*XDIM_MEMBERSET FUNC_AREA = 1100

*XDIM_MEMBERSET PROFITCENTER = DCAT

*XDIM_MEMBERSET RPTCURRENCY = USD

*LOOKUP PLANNING_OP

*DIM AUDITTRAIL = "SKF_DATA"

*DIM CATEGORY = "ACTUAL"

*DIM CONT_COUNTRY = "US"

*DIM ENTITY = "E_NONE"

*DIM RPTCURRENCY = "USD"

*DIM T_ACCT:P_ACCOUNT = "DC_FA_PERC"

*DIM COST_CENTER = "CC_NONE"

*DIM FUNC_AREA = "9150"

*DIM PROFITCENTER = "DCAT"

*DIM TIME = "2016.08"

*ENDLOOKUP

*WHEN CONT_COUNTRY

  *IS US

  *REC(FACTOR = LOOKUP(T_ACCT),PROFITCENTER = DC99,AUDITTRAIL = ABC)

*ENDWHEN

The SOURCE record for this operation contains SIGNEDDATA value as 200.

When I execute the above code the Target Account has value '0'.

Instead of LOOKUP, if I use FACTOR = 2, I get the Target Account value as '400' expectedly.

From the UJKT Log, it is clear that LOOKUP is generating a record which is '0' and hence the original equates the Target Account to '0'.

For the same combination specified in the LOOKUP code, there is value in the backend

UJKT Log:

LGX:

*XDIM_MEMBERSET P_ACCOUNT = C_NONE, DC_FA_PERC

*XDIM_MEMBERSET AUDITTRAIL = PLAN_INPUT, SKF_DATA

*XDIM_MEMBERSET CATEGORY = PLAN,ACTUAL

*XDIM_MEMBERSET COST_CENTER = CC_NONE

*XDIM_MEMBERSET TIME = 2016.08

*XDIM_MEMBERSET ENTITY = E_NONE

*XDIM_MEMBERSET FUNC_AREA = 1100,9150

*XDIM_MEMBERSET PROFITCENTER = DCAT

*XDIM_MEMBERSET RPTCURRENCY = USD

*LOOKUP PLANNING_OP

*DIM AUDITTRAIL = "SKF_DATA"

*DIM CATEGORY = "ACTUAL"

*DIM CONT_COUNTRY = "US"

*DIM ENTITY = "E_NONE"

*DIM RPTCURRENCY = "USD"

*DIM T_ACCT:P_ACCOUNT = "DC_FA_PERC"

*DIM COST_CENTER = "CC_NONE"

*DIM FUNC_AREA = "9150"

*DIM PROFITCENTER = "DCAT"

*DIM TIME = "2016.08"

*ENDLOOKUP

*WHEN CONT_COUNTRY

*IS US

*REC(FACTOR = LOOKUP(T_ACCT) ,PROFITCENTER = DC99,AUDITTRAIL = DUAL_CREDIT)

*ENDWHEN

-------------------------------------------------------------------------------------------------------------------------------------

LOG:

LOG BEGIN TIME:2016-09-25 19:54:13

FILE:\ROOT\WEBFOLDERS\PLANCONSOL_3M \ADMINAPP\PLANNING_OP\TEST.LGF

USER:A6KDFZZ

APPSET:PLANCONSOL_3M

APPLICATION:PLANNING_OP

[INFO] GET_DIM_LIST(): I_APPL_ID="PLANNING_OP", #dimensions=11

AUDITTRAIL,CATEGORY,CONT_COUNTRY,COST_CENTER,ENTITY,FUNC_AREA,MEASURES,PROFITCENTER,P_ACCOUNT,RPTCURRENCY,TIME

#dim_memberset=9

P_ACCOUNT:C_NONE,DC_FA_PERC,2 in total.

AUDITTRAIL:PLAN_INPUT,SKF_DATA,2 in total.

CATEGORY:ACTUAL,PLAN,2 in total.

COST_CENTER:CC_NONE,1 in total.

TIME:2016.08,1 in total.

ENTITY:E_NONE,1 in total.

FUNC_AREA:1100,9150,2 in total.

PROFITCENTER:DCAT,1 in total.

RPTCURRENCY:USD,1 in total.

REC :%value%*(LOOKUP(T_ACCT))

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 0.00 ms. 1  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY LOOKUP DATA FROM APPLICATION: PLANNING_OP

QUERY TIME : 0.00 ms. 1  RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :0.00 ms.

1  RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 1  RECORDS ARE GENERATED.

DATA TO WRITE BACK:

AUDITTRAIL CATEGORY CONT_COUNTRY COST_CENTER ENTITY FUNC_AREA PROFITCENTER P_ACCOUNT RPTCURRENCY TIME SIGNEDDATA

DUAL_CREDIT PLAN US CC_NONE E_NONE 1100 DC99 C_NONE USD 2016.08 0.00

1  RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :1.00  ms.

SCRIPT RUNNING TIME IN TOTAL:2.00 s.

LOG END TIME:2016-09-25 19:54:15

[former_member186338 · Accepted solution]

Sorry, but why do you use LOOKUP at all? In this case (same cube) it's correct to use tuple expression instead of LOOKUP. And the logic is not clear...

[arun_varghese · Accepted solution]

Hi Vadim,

I agree that we can use tuple in *REC, but still checking that ideally whether the code will work or not.

Logic is just a multiplication of 2 data sets: One as defined by the scoping of the source data and the other from the LOOKUP.

[former_member186338 · Accepted solution]

The correct code:

*XDIM_MEMBERSET P_ACCOUNT = C_NONE

*XDIM_MEMBERSET AUDITTRAIL = PLAN_INPUT

*XDIM_MEMBERSET CATEGORY = PLAN

*XDIM_MEMBERSET CONT_COUNTRY = US

*XDIM_MEMBERSET COST_CENTER = CC_NONE

*XDIM_MEMBERSET TIME = 2016.08

*XDIM_MEMBERSET ENTITY = E_NONE

*XDIM_MEMBERSET FUNC_AREA = 1100

*XDIM_MEMBERSET PROFITCENTER = DCAT

*XDIM_MEMBERSET RPTCURRENCY = USD

*WHEN CONT_COUNTRY

*IS *

*REC(FACTOR = ([AUDITTRAIL].[SKF_DATA],[CATEGORY].[ACTUAL],[P_ACCOUNT].[DC_FA_PERC],[FUNC_AREA].[9150]),PROFITCENTER = DC99,AUDITTRAIL = ABC)

*ENDWHEN

Please check data

[former_member186338 · Accepted solution]

P.S. Also TIME is incorrect on your screenshot:

May be you want to have:

*REC(FACTOR = ([AUDITTRAIL].[SKF_DATA],[CATEGORY].[ACTUAL],[P_ACCOUNT].[DC_FA_PERC],[FUNC_AREA].[9150],[TIME].[2006.08]),PROFITCENTER = DC99,AUDITTRAIL = ABC)

[arun_varghese · Accepted solution]

Same result..

Script Logic:

*XDIM_MEMBERSET AUDITTRAIL = PLAN_INPUT

*XDIM_MEMBERSET CATEGORY = PLAN

*XDIM_MEMBERSET COST_CENTER = CC_NONE

*XDIM_MEMBERSET ENTITY = E_NONE

*XDIM_MEMBERSET P_ACCOUNT = C_NONE

*XDIM_MEMBERSET RPTCURRENCY = USD

*XDIM_MEMBERSET PROFITCENTER = DCAT

*XDIM_MEMBERSET FUNC_AREA = 1100

*XDIM_MEMBERSET CONT_COUNTRY = US

*XDIM_MEMBERSET TIME = 2016.08

*WHEN CONT_COUNTRY

  *IS US

  *REC(FACTOR = ([AUDITTRAIL].[SKF_DATA],[CATEGORY].[ACTUAL],[P_ACCOUNT].[DC_FA_PERC],[FUNC_AREA].[9150]),PROFITCENTER = DC99,AUDITTRAIL = DUAL_CREDIT)

*ENDWHEN

UJKT Log:

LGX:

*XDIM_MEMBERSET AUDITTRAIL = PLAN_INPUT

*XDIM_MEMBERSET CATEGORY = PLAN

*XDIM_MEMBERSET COST_CENTER = CC_NONE

*XDIM_MEMBERSET ENTITY = E_NONE

*XDIM_MEMBERSET P_ACCOUNT = C_NONE

*XDIM_MEMBERSET RPTCURRENCY = USD

*XDIM_MEMBERSET PROFITCENTER = DCAT

*XDIM_MEMBERSET FUNC_AREA = 1100

*XDIM_MEMBERSET CONT_COUNTRY = US

*XDIM_MEMBERSET TIME = 2016.08

*WHEN CONT_COUNTRY

*IS US

*REC(FACTOR = ([AUDITTRAIL].[SKF_DATA],[CATEGORY].[ACTUAL],[P_ACCOUNT].[DC_FA_PERC],[FUNC_AREA].[9150]),PROFITCENTER = DC99,AUDITTRAIL = DUAL_CREDIT)

*ENDWHEN

-------------------------------------------------------------------------------------------------------------------------------------

LOG:

LOG BEGIN TIME:2016-09-26 07:36:40

FILE:\ROOT\WEBFOLDERS\PLANCONSOL_3M \ADMINAPP\PLANNING_OP\TEST.LGF

USER:A6KDFZZ

APPSET:PLANCONSOL_3M

APPLICATION:PLANNING_OP

[INFO] GET_DIM_LIST(): I_APPL_ID="PLANNING_OP", #dimensions=11

AUDITTRAIL,CATEGORY,CONT_COUNTRY,COST_CENTER,ENTITY,FUNC_AREA,MEASURES,PROFITCENTER,P_ACCOUNT,RPTCURRENCY,TIME

#dim_memberset=10

AUDITTRAIL:PLAN_INPUT,1 in total.

CATEGORY:PLAN,1 in total.

COST_CENTER:CC_NONE,1 in total.

ENTITY:E_NONE,1 in total.

P_ACCOUNT:C_NONE,1 in total.

RPTCURRENCY:USD,1 in total.

PROFITCENTER:DCAT,1 in total.

FUNC_AREA:1100,1 in total.

CONT_COUNTRY:US,1 in total.

TIME:2016.08,1 in total.

REC :%value%*(([AUDITTRAIL].[SKF_DATA],[CATEGORY].[ACTUAL],[P_ACCOUNT].[DC_FA_PERC],[FUNC_AREA].[9150]))

CALCULATION BEGIN:

QUERY PROCESSING DATA

QUERY TIME : 0.00 ms. 1  RECORDS QUERIED OUT.

QUERY REFERENCE DATA

QUERY TIME : 1.00 ms. 1  RECORDS QUERIED OUT.

CALCULATION TIME IN TOTAL :0.00 ms.

1  RECORDS ARE GENERATED.

CALCULATION END.

ENDWHEN ACCUMULATION: 1  RECORDS ARE GENERATED.

DATA TO WRITE BACK:

AUDITTRAIL CATEGORY CONT_COUNTRY COST_CENTER ENTITY FUNC_AREA PROFITCENTER P_ACCOUNT RPTCURRENCY TIME SIGNEDDATA

DUAL_CREDIT PLAN US CC_NONE E_NONE 1100 DC99 C_NONE USD 2016.08 0.00

1  RECORDS HAVE BEEN WRITTEN BACK.

WRITING TIME :0.00  ms.

SCRIPT RUNNING TIME IN TOTAL:1.00 s.

LOG END TIME:2016-09-26 07:36:41

[former_member186338 · Accepted solution]

As I already mention TIME is incorrect!

[arun_varghese · Accepted solution]

Oh..let me check the time factor

[arun_varghese · Accepted solution]

It worked...time was wrongly selected..silly mistake which caused the confusion

Thanks