Skip to Content

Scenario : How to proceed when your lookup table contains multiple range values?

Snapshot of few fields of source file

Account Groupcode Balance amount
12345 27 43
22345 12 55
32345 51 50
52345 78 70

   

Snapshot of lookup table

XXX1 XXX2 amount selection Groupcode selection
8076 807601 Balance 1;3;15-25;27;29-35
8075 807601 Balance 52-60;65;70;77-79;85
9004 900401 Balance 5

Now you have to lookup the XXX1 and XXX2 value from the lookup table based on the condition : value of sourcefile.groupcode in (lookup table.groupcode selection).

Method 1 : First way is splitting the semi-colons separated in the rows as described in this link http://wiki.sdn.sap.com/wiki/pages/viewpage.action?pageId=283705665 and then apply the logic as shown below :

1. replace – into ..

e.g. replace_substr( ff_final_selection.”Groupcode selection”,’-‘,’..’ )

2. Append it with square brackets in case of range values and then append overall by ms()

e.g.  ‘ms(‘ || ifthenelse( index(Query.”Groupcode selection”,’.’,1) is not null,’\[‘ || Query.”Groupcode selection” || ‘\]’ ,Query.”Groupcode selection” ) || ‘)’

Our final lookup table looks like now :

XXX1 XXX2 amount selection Groupcode selection
8076 807601 Balance ms(1)
8076 807601 Balance ms(3)
8076 807601 Balance ms([15..25])
8076 807601 Balance ms(27)
8076 807601 Balance ms([29..35])
8075 807601 Balance ms([52..60])
8075 807601 Balance ms(65)
8075 807601 Balance ms(70)
8075 807601 Balance ms([77..79])
8075 807601 Balance ms(85)
9004 900401 Balance ms(5)

Now you can easily lookup these values using the operator ‘~’ in the lookup_ext function and get the desired output.

For lookup_ext with ms(). you can follow the link http://wiki.sdn.sap.com/wiki/display/EIM/lookup_ext%28%29+with+pattern

Output will be :

Account Groupcode xxx1 xxx2 Balance amount
12345 27 8076 807601 43
22345 12 null null 55
32345 51 null null 50
52345 78 8075 807601 70

Method2 : Instead of splitting into the rows using a custom function with word_ext function

Function definition is given below :

# Initialization of Variables
$L_CNTR = 1;
$L_CNTR1 = 0;
$L_CNTR2 = 1;
$L_LEN_STR = length($input_field);
$L_OUTPUT_STR = ”;

# Count the no. of semi-colons
while ($L_CNTR <= $L_LEN_STR)
begin
$L_TEMP = substr($input_field,$L_CNTR,1);
if (match_simple($L_TEMP,’;’) = 1)
      $L_CNTR1 = $L_CNTR1 + 1;
$L_CNTR = $L_CNTR + 1;
end
# replace the column value with suitable syntax
while ($L_CNTR2 <= $L_CNTR1+1)
begin
$L_TEMP = word_ext( $input_field,$L_CNTR2,’;’);
if (index($L_TEMP,’.’,1) is not null)
$L_OUTPUT_STR = ($L_OUTPUT_STR || ‘\[‘ || $L_TEMP || ‘\]’);
else $L_OUTPUT_STR = ($L_OUTPUT_STR || $L_TEMP);
if ($L_CNTR2 <> ($L_CNTR1+1))
$L_OUTPUT_STR = ($L_OUTPUT_STR || ‘;’);
else $L_OUTPUT_STR = $L_OUTPUT_STR;
$L_CNTR2 = $L_CNTR2 + 1;
end

$L_OUTPUT_STR = (‘ms(\{‘ || $L_OUTPUT_STR || ‘\})’ );
# While loop ends

Return $L_OUTPUT_STR;

Apply the above fxn on the filed in lookup table and then follow the same steps described in Method1

1 Comment
You must be Logged on to comment or reply to a post.
  • Hi Suraj,

    It is helpful for me .Can you please help me about this question.

    1.I will get new application from BODI/BODS Project. what i need to ask the questions from client. Please let me know.

    2.Can you please explain to the each and every step to Map Operation transformation.

    Please replay ASAP..

    Thanks,

    krish.