# Complex Transformation Routine - Using Internal Tables and SQL

I require assistance with a business scenario in BW 7.0

I have a DSO A, which contains product info for a customer order, the DSO also contains a unique order no field and DSO B, contains the same order no field and also contains all the customers order details. DSO A, apart from the order no shares no common fields with DSO B. I have created a simple overwrite transformation to enhance DSO B with fields from DSO A.

Now with my enhanced DSO B. I want to do the following:

1) Create a new field "total product cost" which sums all the SAME products used in the same order and populates the new field with the total product cost spent by the customer on that product in that order. This amount will appear in each product record in the total product cost field for that particular record where that product is ordered. I assume I will do this in my start routine.

2)Now My DSO B has a new field for for total I want to create a new column which works out the % cost of that product sale order line to the total order value for that product. Essentially taking a percentage of the value of product cost field in that record against the total product cost assigned to the new field in 1). I assume i will do this in my end routine

I assume I will need to use internal tables and loop through for each product/order combination. That would require me populate an internal look up table with each DISTINCT product/order combination loop through each row for the entire records DSO B, and populate the required summed costs - new field and % costs - calculated field.

I tried using SUM and GROUP in a SQL select statement on the internal tables which I populated from DSO B, hoping to write the results of the summation back to the DSO for the appropriate records but I got an error message saying I can only use field name??

I would appreciate any code, advice, suggestions for this very complex transformation routine. Thanks in advance.

##### vamsi talluri replied

Hi,

First map order , product id , customer name,price from dso A to order, product id ,customer name ,amount these are one to one mapping ...........

next in the end routine...

types : begin of itabtype,

order type /bic/oi*,

tot_amt type /bic/oi*,

end of itabtype.

use the name of infoobject.

data : itab type standard table of itabtype,

wa_itab like line of itab.

sort result_pacakge by /bic/* ascending (use technical name of order)

clear wa_itab.

loop at result_package into wa_result_package.

At End of wa_result_package-* . ( use technical name of order)

wa_itab-order = wa_result_package-/bic/*. ( use technical name of order).

wa_itab-tot_amt = wa_itab-tot-amt + wa_result_package-amount.

append wa_itab to itab.

ENDAT.

wa_itab-order = wa_result_package-/bic/*.

wa_itab-tot_amt = wa_itab-tot-amt + wa_result_package-amount.

endloop.

after this loop in the internal table we will bring the order and the total amount...........now we will again update this value in the result_pacakge.

loop at result_package into wa_result_package.

read table itab into wa_itab

with key order = wa_result_package-/bic/*. ( use technical name......)

if sy-subrc = 0.

wa_result_package-/bic/ztotal_order_amount = wa_itab-amount. (use technical name)

endif.

wa_result_package-/bic/prd_sale_% = wa_result_package-/bic/sale_amount / wa_result_package-/bic/ztotal_order_amount * 100.

modify result_package from wa_result_package.

endloop.

i have given the code use the proper names where ever applicable the above code will work ...please change the names accordingly.............

any queries u can get back to me.............

Regards

vamsi