cancel
Showing results for 
Search instead for 
Did you mean: 

BPC 10.0 LOOKUP / ENDLOOKUP

Former Member
0 Kudos

Hi all;

I have a trouble with  my script logic,

*XDIM_MEMBERSET CATEGORY = Actual,Plan1,Plan2,Plan3

*XDIM_MEMBERSET TIME = 2012.01

*XDIM_MEMBERSET GOSTERGELER = GiderTutari

*XDIM_MEMBERSET INPUTCURRENCY = TRY,EUR,USD,GBP

 

*LOOKUP Arete_Demo

*DIM CATEGORY = "$Alloc_Plan$"

*DIM TIME = "BAS($Alloc_where_var$)"

*DIM ENF:GOSTERGELER = "Enf_Oran"

*DIM ENTITY = "CSO"

*DIM INPUTCURRENCY = "TRY"

*ENDLOOKUP

*COMMIT

*RUNALLOCATION

*FACTOR = (100+LOOKUP(ENF))/100*(USING/TOTAL)

*DIM MASRAFYERI                   WHAT = BAS(MY);                       WHERE = BAS(MY);                                USING = <<<;

*DIM MASRAFCESIDI                WHAT = BAS(MC);                       WHERE = BAS(MC);                                USING = <<<;

*DIM GOSTERGELER                WHAT = GiderTutari;                    WHERE = GiderTutari;                              USING = <<<;

*DIM CATEGORY                      WHAT = Actual;                          WHERE = $Alloc_Plan$;                           USING = Actual;

*DIM TIME                               WHAT = BAS($Alloc_what_var);     WHERE = BAS($Alloc_where_var$);           USING = BAS($Alloc_what_var$);

*ENDALLOCATION

*COMMIT

The package status : SUCCESS

But the system gives " Reference Error: LOOKUP is not defined"

In BPC 10.0, do you know any change in LOOKUP function?

Cause I tried to write in *FACTOR row: "[LOOKUP].[ENF] " , "[LOOKUP].[#ENF]", and etc..

Do you have any idea about this error?

Please help me.

Best regards

Haşim

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Gersh,

Thank you for valuable answer.

But I wonder the answer of this question, How can I write dynamic script with BPC 10.0 SP05?

Which functions help me?

I think it doesn't necessary to write my script, You can look original post. It is clear enough.

Please help me.

Best regards

Haşim

former_member200327
Active Contributor
0 Kudos

Hi Haşim,

I didn't reply to your original posting because I didn't understand what your original script is supposed to do. Reason is, LOOKUP can return only a single value for a single call and you want to put multiple members with same (empty) ID in TIME dimension.You can try using FOR/NEXT loop as Vadim suggested to generate something like

DIM 2011.01:TIME = 2011.01

DIM 2011.02:TIME = 2011.02

etc. and that's where that OSS Note comes into play. Later when you want to use that LOOKUP you use another variable that loops on same periods and use LOOKUP(that variable), but I doubt this will work with RUNALLOCATION. You have a better case with WHEN/REC/ENDWHEN.

If you are trying to use RUNALLOCATION with calculations form another Model then I don't think it's possible currently in NW version unless you retrieving a constant form that other Model.

Regards,

Gersh

Former Member
0 Kudos

Hi Gersh,

Let me explain the aim of my script;

I had an actual data from past years, I want to generate plan data with to multiply inlation rate. So I am selected source year by $Alloc_what_var$, target year by $Alloc_where_var$, plan version by $Alloc_plan$.

For example $Alloc_what_var$ = 2012.TOTAL, I writed in BAS statement because, 2012.TOTAL is not a base level so I am turned 12 months for 2012.

And also I collected inflation rate from users via input schedules. But this data is in a another model. In my original post, inflation rate is in Arete Demo Model. But my calculation is in Demo Gider Model

For example;

Let's say the User Selections are:  Source Year = 2012.TOTAL; Target Year = 2013.TOTAL; Plan Version = Plan1

According to this selection, I waited for the sytem that [2012.01 actual data * (100 + inflation rate(2013.01)) / 100] to write plan data for the 2013.01, Plan1.

And inflation rate must be 2013.01, Plan1 data.

And finally system will have to do same things for 2013.02,2013.03.......2013.12 Plan1 data.

So I guess I have to use LOOKUP function if else you know different function to do same job.

I hope it is clear.

Best regards

Haşim

former_member186338
Active Contributor
0 Kudos

Hi Haşim,

Due to script logic limitations it's not possible to create script exactly according to your input specifications. But if we use year as "2012" instead of "2012.TOTAL" then:

PROMPT(COMBOBOX,%Alloc_what_var%,"Choose source year: ",0,,{2011,2012,2013,2014})

PROMPT(COMBOBOX,%Alloc_where_var%,"Choose target year: ",0,,{2011,2012,2013,2014})

Script:

*XDIM_MEMBERSET CATEGORY = Actual

*XDIM_MEMBERSET GOSTERGELER = GiderTutari

*XDIM_MEMBERSET INPUTCURRENCY = $Kur_Demo$

*XDIM_MEMBERSET MASRAFYERI= MY_1

*XDIM_MEMBERSET MASRAFCESIDI= MC_1

*LOOKUP Arete_Demo

*DIM CATEGORY = "$Alloc_Plan$"

*FOR %MNTH%=JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC

*DIM M_%MNTH%:TIME = "$Alloc_where_var$.%MNTH%"

*NEXT

*DIM GOSTERGELER = "Enf_Oran"

*DIM ENTITY = "CSO"

*DIM INPUTCURRENCY = "TRY"

*ENDLOOKUP

*FOR %MNTH%=JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC

*XDIM_MEMBERSET TIME=$Alloc_what_var$.%MNTH%

*WHEN GOSTERGELER

*IS "GiderTutari"

  *REC(FACTOR = (100+LOOKUP(M_%MNTH%))/100,CATEGORY = $Alloc_Plan$,TIME = $Alloc_where_var$.%MNTH%)

*ENDWHEN

*NEXT

*COMMIT

B.R. Vadim

Message was edited by: Vadim Kalinin *REC(FACTOR = (100+LOOKUP(M_%MNTH%))/100*%VALUE%... changed to: *REC(FACTOR = (100+LOOKUP(M_%MNTH%))/100...

Former Member
0 Kudos

Thanks a lot Vadim,

It is working, only I changed months like that %MONTH%=01,02,03,.....,12

Best regards,

Haşim

former_member200327
Active Contributor
0 Kudos

Hi Haşim,

You can even use regular selection from TIME Dimension in your DM package and then loop on base members of that selection. You just have to apply that OSS Note I mentioned above.

It just won't work for RUNALLOCATION.

Regards,

Gersh

Answers (2)

Answers (2)

jrg_finster3
Active Participant
0 Kudos

Hi,

please remove the first *COMMIT in your script and try again

Regards

Jörg

hey, nearly simultaneous

Former Member
0 Kudos

I tried but error is the same.

Anything else?

former_member210696
Active Contributor
0 Kudos

Hi Hasim,

I think there might be a problem in this statement -

*DIM TIME = "BAS($Alloc_where_var$)"

When using the lookup, I am not sure you can have multiple dimension members assigned to one dimension,  i believe the rate stored in rate app will be different in all the base members of time dimension member which you are referring to.

Not sure how will system identify and fetch that rate.

Please try using a simple time dimension member and see if it works.

Former Member
0 Kudos

You may be right but I need dynamic solution so I have to write the Time statement like BAS().

I gave up allocation package so I try to ICDATA type.

And ı change my script to this version

*XDIM_MEMBERSET CATEGORY = Actual

*XDIM_MEMBERSET TIME = BAS($Alloc_what_var$)

*XDIM_MEMBERSET GOSTERGELER = GiderTutari

*XDIM_MEMBERSET INPUTCURRENCY = $Kur_Demo$

*XDIM_MEMBERSET MASRAFYERI= MY_1

*XDIM_MEMBERSET MASRAFCESIDI= MC_1

*LOOKUP Arete_Demo

*DIM CATEGORY = "$Alloc_Plan$"

*DIM TIME = "BAS($Alloc_where_var$)"

*DIM ENF:GOSTERGELER = "Enf_Oran"

*DIM ENTITY = "CSO"

*DIM INPUTCURRENCY = "TRY"

 

*ENDLOOKUP

*WHEN GOSTERGELER

*IS "GiderTutari"

*REC(FACTOR = (100+LOOKUP(ENF))/100*%VALUE%,CATEGORY = "$Alloc_Plan$",TIME = "BAS($Alloc_where_var$)")

*ENDWHEN

*COMMIT

And in package :

PROMPT(COMBOBOX,%Alloc_Plan%,"Choose Plan Version: ",0,,{Plan1,Plan2,Plan3})

PROMPT(COMBOBOX,%Alloc_what_var%,"Choose source year: ",0,,{2011.TOTAL,2012.TOTAL,2013.TOTAL,2014.TOTAL})

PROMPT(COMBOBOX,%Alloc_where_var%,"Choose target year: ",0,,{2011.TOTAL,2012.TOTAL,2013.TOTAL,2014.TOTAL})

PROMPT(COMBOBOX,%Kur_Demo%,"Choose Currency: ",0,,{TRY,USD,EURO,GBP})

TASK(/CPMB/ICDATA_LOGIC,SUSER,%USER%)

TASK(/CPMB/ICDATA_LOGIC,SAPPSET,%APPSET%)

TASK(/CPMB/ICDATA_LOGIC,SAPP,%APP%)

TASK(/CPMB/ICDATA_LOGIC,SELECTION,%SELECTION%)

TASK(/CPMB/ICDATA_LOGIC,LOGICFILENAME,ENFLASYON1.LGF)

INFO(%EQU%,=)

INFO(%TAB%,;)

TASK(/CPMB/ICDATA_LOGIC,TAB,%TAB%)

TASK(/CPMB/ICDATA_LOGIC,EQU,%EQU%)

TASK(/CPMB/ICDATA_LOGIC,REPLACEPARAM,Kur_Demo%EQU%%Kur_Demo%%TAB%Alloc_Plan%EQU%%Alloc_Plan%%TAB%Alloc_what_var%EQU%%Alloc_what_var%%TAB%Alloc_where_var%EQU%%Alloc_where_var%)

But I still have failed.

This screenshot is below,

I can't understand in allocation packages the system can read  BAS statements, but LOOKUP is not, It is not logical

former_member186338
Active Contributor
0 Kudos

Hi, Hasim!

The syntax: *DIM TIME = "BAS($Alloc_where_var$)" is definitely wrong. Skip this line at all. And try your original script.

B.R. Vadim

former_member186338
Active Contributor
0 Kudos

And *REC(FACTOR = (100+LOOKUP(ENF))/100*%VALUE%,CATEGORY = "$Alloc_Plan$",TIME = "BAS($Alloc_where_var$)") is also wrong for TIME. One REC statement will write only one value.

For LOOKUP it was tested that it's not possible to create in the loop TIME labels using FOR NEXT - LOOKUP label can't contain "."

Vadim

Former Member
0 Kudos

Hi Vadim,

Thank you for clarification.

I will try FOR NEXT statement, I hope it solves.

former_member186338
Active Contributor
0 Kudos

With FOR/NEXT you will not be able to define LOOKUP labels for TIME using the month ID because month ID contains "."

The label like L_2013.01 is invalid.

B.R. Vadim

former_member200327
Active Contributor
0 Kudos

Issue with period in ID is solved in SP07. Now all characters that are allowed in member IS are allowed in LOOKUP DIM.

former_member186338
Active Contributor
0 Kudos

Hi, Gersh

The issue is solved for BPC 10 only?

B.R. Vadim

former_member200327
Active Contributor
0 Kudos

Yes, this is correct, but you can send a message to OSS and they'll downport it to 7.5x.

Former Member
0 Kudos

Hi Gersh;

I have already using BPC 10.0 SP05, maybe I understood wrong. Please correct me.

This issue is solved BPC 10.0?

And what do you mean downport 7.5x?

Best Regards

Haşim

former_member200327
Active Contributor
0 Kudos

Hi Haşim,

Sorry, I was wrong. Issue will be resolved in BPC10.0 SP08 (not in SP07 this is already released).

Meanwhile you can apple OSS Note1726001 that solves it.

Right now support mostly solves issues first in BPC 10.0 and then downports code to BPC 7.5.

Hope this makes it more clear.

Regards,

Gersh

former_member210696
Active Contributor
0 Kudos

Hello Hasim,

I don't really see any problem with the Lookup statement that you have used.

Can you try using the Lookup in a simple *REC statement something as below -

*REC(EXPRESSION=%VALUE%/LOOKUP(ENF), RPTCURRENCY="EUR")

See if it is working.

I presume the error that you have mentioned is during validation.

btw..what is the account dimension in Rate application, is it being defined in the lookup statement? I don't think it is??

Hope it helps!!

Former Member
0 Kudos

Hello Ashish,

Thank you for quick reply,

I tried what you say, And my script is:

*XDIM_MEMBERSET CATEGORY = Actual

*XDIM_MEMBERSET TIME = 2012.01

*XDIM_MEMBERSET GOSTERGELER = GiderTutari

*XDIM_MEMBERSET INPUTCURRENCY = TRY

*XDIM_MEMBERSET MASRAFYERI = MY_1

*XDIM_MEMBERSET MASRAFCESIDI = MC_1

*LOOKUP Arete_Demo

*DIM CATEGORY = "Plan1"

*DIM TIME = 2013.01

*DIM ENF:GOSTERGELER = "Enf_Oran"

*DIM ENTITY = "CSO"

*DIM INPUTCURRENCY = "TRY"

*ENDLOOKUP

*WHEN INPUTCURRENCY

*IS "TRY"

*REC(EXPRESSION=%VALUE%*(100+LOOKUP(ENF))/100, TIME="2013.01", CATEGORY="Plan1")

*ENDWHEN

*COMMIT

It ıs working

But in allocation, why LOOKUP is not defined?

Do you have any idea?

I tried to add WHEN statement but it is not.

former_member210696
Active Contributor
0 Kudos

Hasim,

Please try without the commit statement immediately after endlookup.