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

[Former Member]

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