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
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.