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

Snapshot of few fields of source file

Snapshot of lookup table

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 :

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 :

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