How to look up a custom table in the transformation

 This post describes the procedure to look up a custom table in bw transformation. The business scenario can be calculation of certain key figures based on the entries in the table.

This table can be custom table i.e. created by the user or any standard table too.

The first step for doing a look up is to store all the table entries or selected table entries in a global internal table. This is because we need to read the custom table once instead of reading entries in the field routine otherwise data load performance significantly gets impacted.

This reading of table entries and storing them in the internal table happens in the start routine. Here the internal table must be declared in the global part so that is gets recognized by the field routine.

 

Step 1:

Make sure that the custom table which will be looked up are active and have correct table entries. I am taking 2 sample names for these: ZCUSTOM_TABLE1 and ZCUSTOM_TABLE2.

Step 2:

Declare internal tables in the global part of the start routine:

*$*$ begin of global - insert your declaration only below this line  *-*
     ... "insert your code here
 DATA ITAB1 TYPE TABLE OF ZCUSTOM_TABLE1.
 DATA ITAB2 TYPE TABLE OF ZCUSTOM_TABLE2.
 
 *$*$ end of global - insert your declaration only before this line   *-*
 
     METHODS
       start_routine
         IMPORTING
           request                  type rsrequest
           datapackid               type rsdatapid 
.........
.........

Here ITAB1 and ITAB2 are declared as internal tables to store the values of the custom tables once in a data load.

Step 3:

Store the entries in the internal table by reading the custom tables using Select or Select *

Now Scroll down in the same start routine, write below code in the portion as shown-

*$*$ begin of routine - insert your code only below this line        *-*
 ... "insert your code here
 *--  fill table "MONITOR" with values of structure "MONITOR_REC"
 *-   to make monitor entries
 ... "to cancel the update process
 *    raise exception type CX_RSROUT_ABORT.
 
 SELECT * FROM ZCUSTOM_TABLE1 INTO TABLE ITAB1.
 SELECT * FROM ZCUSTOM_TABLE2 INTO TABLE ITAB2.
 
 *$*$ end of routine - insert your code only before this line         *-*
   ENDMETHOD.                    "start_routine
 *----------------------------------------------------------------------* 

Check and save the start routine, come out of it and activate your transformation.

Step 4:

Read the internal table entries in the field routine or in the end routine to do any calculations / manipulations

e.g. In a field routine, I want to calculate a key figure based on entries in the table ITAB1

*$*$ begin of routine - insert your code only below this line        *-*
     ... "insert your code here
 *--  fill table "MONITOR" with values of structure "MONITOR_REC"
 *-   to make monitor entries
     ... "to cancel the update process
 *    raise exception type CX_RSROUT_ABORT.
     ... "to skip a record"
 *    raise exception type CX_RSROUT_SKIP_RECORD.
     ... "to clear target fields
 *    raise exception type CX_RSROUT_SKIP_VAL. 

IF <your condition>    "Optional

DATA WA LIKE LINE OF ITAB1.

LOOP AT ITAB1 INTO WA WHERE <your condition> AND/ OR <your condition>.

 IF SY-SUBRC = 0.

  RESULT  = SOURCE_FIELDS-/BIC/<your infoobject>.

 ELSE.

  RESULT = ' '.

 ENDIF.

ENDLOOP.

ENDIF.

*$*$ end of routine - insert your code only before this line         *-*
   ENDMETHOD.                    "compute_ZBALANCE

Check and save your routine, activate your transformation.

Do a sample data load to check the calculations and logic.