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 https://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 https://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
New NetWeaver Information at SAP.com
Very Helpfull