Skip to Content

OK, let’s start to find out the answer about one of the questions that we had in the last post.

 

“How can I get the value from the other application?”
The simple answer is… USE *LOOKUP/*ENDLOOKUP!

The simplest example is the currency conversion because you need to read rate value from the rate application to convert

currency values of the finance application.

 (NOTE:*LOOKUP/*ENDLOOKUP also can be used for reading value of the current application.)

Here is the syntax of *LOOKUP/*ENDLOOKUP
    
The syntax is: 
 
*LOOKUP {Application}
*DIM [{LookupID}:] {DimName}=”Value” | {CallingDimensionName}[.{Property}]
[*DIM …] 
*ENDLOOKUP
 
 
   {Application} is the name of the application which you will retrieve value.
 
   {DimensionName} is a dimension in the lookup application.
 
   {CallingDimensionName} is a dimension in the current application.
 
   {LookupID} is an optional variable that will hold the value so that you can use it in the script.
              This is only required when multiple values must be retrieved.      

Now, let’s do it step by step.

Here are our requirements for the currency conversion.
1. You need to get the rate values from rate application for currency conversion (LC to USD and EUR). 
2. The member id of RATE dimension in the rate application should be the same as RATETYPE property of the account dimension in the finance application.
3. The member id of RATEENTITY dimension in the rate application should be “DEFAULT”
4. The rule of currency conversion is ‘DESTINATION CURRENCY/CURRENT CURRENCY’
  
    
First, you need to define *LOOKUP with application name.
   *LOOKUP RATE
    
   *ENDLOOKUP

Second, specify dimensions of RATE application with *DIM statement.
   (Let’s assume the rate application has RATEENTITY, INPUTCURRENCY, RATE, CATEGORY and TIME dimension.)  

   *LOOKUP RATE
     *DIM RATEENTITY
     *DIM INPUTCURRENCY
     *DIM RATE
     *DIM CATEGORY
     *DIM TIME
   *ENDLOOKUP
 
Third, assign the member id value of each dimension from the current application (Finance) or use fixed value.
       If you need to retrieve multiple value according to different member id values of specific dimensions,
       Make copies of that dimension and assign different values.
      
   *LOOKUP RATE
     *DIM RATEENTITY=”DEFAULT”      // Fixed value
     *DIM INPUTCURRENCY=”USD”       // Fixed value
     *DIM INPUTCURRENCY=”EUR”       // Fixed value, Copy same dimension for another value
     *DIM INPUTCURRENCY=ENTITY.CURR // added one more for the currency conversion as variable value
     *DIM RATE=ACCOUNT.RATETYPE     // Variable value based on the current application
     *DIM CATEGORY
     *DIM TIME
   *ENDLOOKUP

Fourth, Put variables for multiple retrieving values in front of each duplicate dimension name. 

   *LOOKUP RATE
     *DIM RATEENTITY=”DEFAULT”              
     *DIM DESTCURR1:INPUTCURRENCY=”USD”     
     *DIM DESTCURR2:INPUTCURRENCY=”EUR”     
     *DIM SOURCECUR:INPUTCURRENCY=ENTITY.CURR
     *DIM RATE=ACCOUNT.RATETYPE               
     *DIM CATEGORY
     *DIM TIME
   *ENDLOOKUP

   ————————————————————————-
   Note: If you want to get some value based on two or more dimensions,
         You should use the same variable name when you map dimensions.
         Here is an example.

         *LOOKUP OWNERSHIP
             *DIM INTCO=”IC_NONE”
             *DIM PARENT=”MYPARENT”

             *DIM PCON:ACCOUNTOWN=”PCON”      // PCON is used for ACCOUNTOWN
             *DIM PCON:ENTITY=ENTITY          // PCON is used for ENTITY

             *DIM IC_PCON:ACCOUNTOWN=”PCON”    // IC_PCON is used even though it searches same “PCON”
             *DIM IC_PCON:ENTITY=INTCO.ENTITY  // IC_PCON is used for INTCO.ENTITY
         *ENDLOOKUP

         Even though the member id of ACCOUNTOWN dimension is same, the variable should be defined as a different variable because the member id of ENTITY dimension is different in the combination.
 
         If the ‘ENTITY’ property of INTCO dimension has I_FRANCE value, above *LOOKUP will select below two records and each variable will have different value.

             IC_NONE,MYPARENT,PCON,FRANCE,100    => PCON
             IC_NONE,MYPARENT,PCON,I_FRANCE,80   => IC_PCON
   —————————————————————————
 

Last, Remove dimension names (TIME and CATEGORY> that don’t have any fixed value or variable value because it will be passed as current value

automatically. 
 
   *LOOKUP RATE
     *DIM RATEENTITY=”DEFAULT”   
     *DIM SOURCECUR:INPUTCURRENCY=ENTITY.CURR
     *DIM DESTCURR1:INPUTCURRENCY=”USD”
     *DIM DESTCURR2:INPUTCURRENCY=”EUR”
     *DIM RATE=ACCOUNT.RATETYPE 
   *ENDLOOKUP
  
Now we get the values so how can we use these values?
You can use it using LOOKUP(Variable) in your *REC statement as below

   *WHEN ACCOUNT.RATETYPE
      *IS “AVG”,”END”
         *REC(FACTOR=LOOKUP(DESTCURR1)/LOOKUP(SOURCECURR),CURRENCY=”USD”)
         *REC(FACTOR=LOOKUP(DESTCURR2)/LOOKUP(SOURCECURR),CURRENCY=”EUR”)
   *ENDWHEN

   NOTE: You can use LOOKUP(variable) with *WHEN and *IS statement.

       Ex) *WHEN LOOKUP(PCON)          //as a condition value of when
              *IS <= LOOKUP(IC_PCON)   //as a value of IS
                  *REC(FACTOR=-1, PARENT =”MYPARENT”,DATASRC=”ELIM”)
           *ENDWHEN 

We reviewed how to define *LOOKUP/*ENDLOOKUP statement and how to use it.

Now it is time to find out how it works in the script engine.

Let’s assume below records are in the rate application and see what will happen during execute of the script logic.

RATEENTITY, INPUTCURRENCY, RATE, CATEGORY, TIME, SIGNEDDATA

DEFAULT, USD, AVG, ACTUAL, 2011.JAN, 1
DEFAULT, EUR, AVG, ACTUAL, 2011.JAN, 1.22
DEFAULT, CHF, AVG, ACTUAL, 2011.JAN, 0.91
DEFAULT, USD, END, ACTUAL, 2011.JAN, 1
DEFAULT, EUR, END, ACTUAL, 2011.JAN, 1.24
DEFAULT, CHF, END, ACTUAL, 2011.JAN, 0.93
RATCALC, USD, AVG, ACTUAL, 2011.JAN, 1
RATCALC, USD, AVG, ACTUAL, 2011.JAN, 1

Here are your current finance application records that need to be processed.

ACCOUNT, ENTITY, CATEGORY,  TIME, CURRENCY, SIGNEDDATA
INVENTORY, SWITZERLAND, ACTUAL, 2011.JAN, LC, 5000
REVENUE,   SWITZERLAND, ACTUAL, 2011.JAN, LC, 1000

As you can see, there is no relationship between finance application and rate application.
We know Switzerland currency is CHF but there is no information in each fact table record.
It only has LC (local currency) value.

Then, how can script logic find the value and calculate it?

The key point is ‘ENTITY.CURR’ which we used it for mapping dimension as below.

    *DIM SOURCECUR:INPUTCURRENCY=ENTITY.CURR

ENTITY.CURR means ‘CURR’ property value of ENTITY dimension.
Therefore, Switzerland which is one of the Entity dimension member should have ‘CHF’ value in its ‘CURR’ property.

Same thing is for mapping RATE dimension of rate application as below.
    *DIM RATE=ACCOUNT.RATETYPE

So the ‘RATETYPE’ property value of INVENTORY and REVENUE account should have ‘AVG’ or ‘END’ value.

Therefore, the Script engine will do the following steps to process the first record of the fact table.

1. Select RATEENTITY = “DEFAULT” 

2. Select INPUTCURRENCY = “CHF” (because current Entity member’s ‘CURR’ property value is ‘CHF’)
   OR INPUTCURRENCY = “USD”     
   OR INPUTCURRENCY = “EUR”      

3. Select RATE = “END” (because current account member’s ‘RATETYPE’ property value is ‘END’)

4. Select CATEGORY = “ACTUAL” (There is no statement so it is same as current application CATEGORY value.)

5. Select TIME = “2011.JAN” (There is no statement so it is same as current application TIME value.)

All above selection will be combined with ‘AND’ condition.

So the 3 records below will be selected and its signeddata value will be assigned to each variable.

DEFAULT, USD, END, ACTUAL, 2011.JAN, 1    => DESTCURR1 will be 1
DEFAULT, EUR, END, ACTUAL, 2011.JAN, 1.24 => DESTCURR2 will be 1.24
DEFAULT, CHF, END, ACTUAL, 2011.JAN, 0.93 => SOURCECUR will be 0.93

After the script logic engine executes below statements, it will generate 2 records.

   *WHEN ACCOUNT.RATETYPE
      *IS “AVG”,”END”
         *REC(FACTOR=LOOKUP(DESTCURR1)/LOOKUP(SOURCECURR),CURRENCY=”USD”)
         *REC(FACTOR=LOOKUP(DESTCURR2)/LOOKUP(SOURCECURR),CURRENCY=”EUR”)
   *ENDWHEN

ACCOUNT, ENTITY, CATEGORY,  TIME, CURRENCY, SIGNEDDATA
INVENTORY, SWITZERLAND, ACTUAL, 2011.JAN, LC,  5000
INVENTORY, SWITZERLAND, ACTUAL, 2011.JAN, USD, 5376.34     // 5000 * (1/0.93)
INVENTORY, SWITZERLAND, ACTUAL, 2011.JAN, EUR, 6666.67     // 5000 * (1.24/0.93)

For the 2nd record in the fact table, the 3 records below will be selected from the rate application fact table because

Revenue account has ‘AVG’ RATETYPE.

DEFAULT, USD, AVG, ACTUAL, 2011.JAN, 1
DEFAULT, EUR, AVG, ACTUAL, 2011.JAN, 1.22
DEFAULT, CHF, AVG, ACTUAL, 2011.JAN, 0.91

After it processes ‘REVENUE’ records, there will be 6 records in the fact table as below.
(4 records will be generated in total.)

ACCOUNT, ENTITY, CATEGORY,  TIME, CURRENCY, SIGNEDDATA
INVENTORY, SWITZERLAND, ACTUAL, 2011.JAN, LC,  5000
INVENTORY, SWITZERLAND, ACTUAL, 2011.JAN, USD, 5376.34    
INVENTORY, SWITZERLAND, ACTUAL, 2011.JAN, EUR, 6666.67    
REVENUE,   SWITZERLAND, ACTUAL, 2011.JAN, LC,  1000
REVENUE,   SWITZERLAND, ACTUAL, 2011.JAN, USD, 1098.90     // 1000 * (1/0.91)
REVENUE,   SWITZERLAND, ACTUAL, 2011.JAN, EUR, 1340.66     // 1000 * (1.22/0.91)

 

We finished learning how to use *LOOKUP/*ENDLOOKUP statement.

Here are some questions and answers that I got frequently.

Question 1: What if rate application doesn’t have the value?
            Then currency conversion will not happen.

Question 2: I don’t have any records in the fact table of current application. What will happen?
            The script logic always reads records from the current application.
            Therefore, if there are no records in the fact table of the current application,
            Nothing will happen.

Question 3: Can we lookup parent member value instead of base member (leaf member)?
            MS version can do it with *OLAPLOOKUP statement instead of *LOOKUP but NW version doesn’t have it yet.

  
I will explain about *FOR/*NEXT in the next post.

To report this post you need to login first.

12 Comments

You must be Logged on to comment or reply to a post.

  1. Pankaj Patil
    Thanks James for the blog.
    One question – Suppose a certain dimension in lookup..endlookup is not mention (in the case of blog it is ‘time’ and ‘category’) and has multiple values in the application. What will the output be in this case ?
    Regards.
    (0) 
    1. James Lim Post author
      Hi, Pankaj
      I am sorry for late reply. I was on my business trip to Korea and Las Vegas.

      If that dimension is same name as Calling application; – (in the example, it will be Finance) – It will be passed same value to filter it.

      As you can see in the example, Category value Actual was passed to Rate application from Finance application to filter it.

      Please remember that lookup should find a value to use it in the calling application.

      I hope it answers your question.

      Regards,
      James Lim

      (0) 
  2. Jack SAP

    Hi James

    Thanks for sharing your knowledge with these excellent examples, you really made our life simple.. 😉

    One Question:

    I am trying to import REVENUE amount from different applications to my main P&L(Profit and Lost) application via Lookup function.. ie calling from P&L application.

    But since my current P&L application does not have records in fact table so my code is not working ( you also mentioned this in Question 2) ..as a work around ie for testing purpose I am writing value 1 in fact table and running it which is not appropriate solution 🙁

    So shall I try with DESTINATION_APP function or there is a way to tackle this ? 😕

    Appreciate your Quick response.

    Cheers!!!

    “Keep smiling, because life is a beautiful thing and there’s so much to smile about.”

    Marilyn Monroe

    (0) 
    1. James Lim Post author

      Arjun,

      you can put Zero value record for the current application.   BUT!!!

      As you know, user can input 0 value through input template,  🙁

      But here is a simple trick. you can input 1 and it will work and then make it 0 again.

      BPC is using calculate delta so WB table will have two records one is 1 and the other one is -1.

      Therefore, your logic will work. 

      To make better way using this, you can do a kind of initalization job through data manager.

      With using Append mode, you can import 1 and -1 value to the account that you want so that next logic job can work without any problem.

      I hope it will help you.

      Regards,

      James Lim

      (0) 
      1. Jack SAP

        Hi James

        Thanks for your Quick response 🙂 right now I am trying with Destination_APP and will also try with your above method..Will definitely update once done..Thanks

        Cheers!!!

        “Keep smiling, because life is a beautiful thing and there’s so much to smile about.”

        Marilyn Monroe

        (0) 
      2. Jack SAP

        Hi James

        Sorry for the late reply, I have tried for my case with Destination_App (ie pushing data from all application to main PL application) and which is very easy to get results 🙂 ..I believe many of us like freshers in BPC are using your thread(script-logic-for-dummies) as a Bible for script logic concept, if possible please update your thread with Destination_App functionality 😛

        Once again thanks for your support.

        Cheers!!!

        “Keep smiling, because life is a beautiful thing and there’s so much to smile about.”

        (0) 
      1. Dav ...

        Hi James

        What about this issue

        I am working on Script Logics in SAP BPC and have been trying to send the data from one application to other. I used “LOOKUP” for this. I have successfully tested the logic and seen the data coming from source applications. But I have an issue: When i run data manager Package to get the data in target application  from other source applications. Data don’t show up. On the other hand, when I create input schedule in target application and enter “1” and send it and after that when I run the Data Manager Packages. Data do come from current application from source apps. Please help out, why do i need to enter 1. Without entering 1, cant I use just Data Manager Packages just to call data. If anyone has come across to this issue please assist me to get over it.

        I know that I can use DESTINATION_APP, but I just want to use LOOKUP

        Thanks

        Varun

        (0) 

Leave a Reply