Complex Transformation Routine - Using Internal Tables and SQL

[Former Member]

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.

[Former Member · Accepted solution]

Hi McCavoy,

Can You please provide three or four records in the two DSO and the output how u want for the DSO B. Then it will be gt8 and it will be easier to give a solution for ur problem...

Regards

vamsi

[Former Member · Accepted solution]

Ok this is an example of the two DSOs. The first table is DSO A used to "enhance" DSO B. The DSO B in the second table shown below is prior to applying the transformation routine. It has two new "empty" fields ready for the results of the transformation. The third table shows the DSO B after transformation with enhance Customer details and the two new fields populated.

DSO A

ProductID Product_Name Market Customer Channel Order Price

00001 chair DIY Mickey Mouse DIY Stores 123456 50

00002 sinks Plumbing Scrappy Doo Trade Hardware Stores 234567 150

00003 TV Home Ent. Batman Electronics Stores 345678 500

00004 chair DIY Minnie Mouse DIY Stores 123456 50

00005 computer Home Ent. Scooby Doo Electronic Stores 234567 600

00006 DVD player Home Ent. Scrappy Doo Electronic Stores 234567 250

00007 TV Stand Furniture Robin Furniture Stores 345678 500

DSO B - Prior to Transformation

Order ProductID Sale_Amount Total_Order_Sale_Amount % of Total Prd Sale

123456 00001 50

123456 00004 50

234567 00002 150

234567 00005 600

234567 00006 250

345678 00003 500

345678 00007 500

DSO B - Post Transformation

Customer Order ProductID Sale_Amount Total_Order_Sale_Amount % of Total Prd Sale

Mickey Mouse 123456 00001 50 100 50

Minnie Mouse 123456 00004 50 100 50

Scrappy Doo 234567 00002 150 1000 15

Scooby Doo 234567 00005 600 1000 60

Scrappy Doo 234567 00006 250 1000 25

Batman 345678 00003 500 1000 50

Robin 345678 00007 500 1000 50

I hope this illustrates what I am trying to achieve with this routine. Any code, routine template, advice etc will be most appreciated. Thanks again in advance.

[Former Member · Accepted solution]

Hi

I have two small queries

1) Is the source for transformation of DSO B is DSO A only.......? or it it getting data as u showed before transformation from some other datasource and the two fields data should be brought from

DSO A.

2) Can there be many customers for a single order like u can given multiple customer details...Mickey Mouse and Minnie Mouse for a particular order say 123456.

No prob this can be achieved

1) if source for transformation for DSO B is DSO A then

u can easily achieve this in end routine... in end routine u can create a internal table and there u can get total amount of each order .

using this internal table we will update the result_package which will be your final output and at the same time we will calculate the % of total prd sale.

Here as the source will be DSO A we can have order id, Product id,Customer Name directly taken from DSO A.

Here multiple customer name is not a problem for a particular order because those will be directly mapped from DSO A to DSO B.

2) if source of transformation for DSO B is not DSO A but some other datasource which provides data as u shown post transformation then

we will create a internal table and get the total amount against each order....

in the end routine we will loop over the result package and in the result package which be the output

there

as the customer name is changing for a particular order based on the order and product id i will bring the customer name and directly update in the result_package for every record...

and for the total amount we will read from the internal table and update in the result_pacakge for every record and based on this value we will calculate the % of total prd sale.........

hope you understand ............any queries get back to me and tell me which is your case either it is first or second one........

Regards

vamsi

[Former Member · Accepted solution]

Hi the answer to both queries

1) Is the source for transformation of DSO B is DSO A only.......? or it it getting data as u showed before transformation from some other datasource and the two fields data should be brought from

DSO A.

Ans. Yes DSO A is the only source for transformation of DSO B. DSO B is populated from a data source before the transformation from DSO A is to be applied.

2) Can there be many customers for a single order like u can given multiple customer details...Mickey Mouse and Minnie Mouse for a particular order say 123456.

Ans. Yes there can be multiple customers for a single order since correct % cost contribution to the total sale will be assigned for each multiple record.

Please could you provide some generic code or template code for this routine in BW 7.0. I have been struggling a bit design and the syntax in BW 7.0 as I am more use to BW 3.5 format.

Thanks in advance for your help.

[Former Member · Accepted solution]

Hi McCavoy

I am little confused with your first answer.

datasource----dsoA -

---

dsoB

or

datasource -

---

dsoB.

look up from dsoA

please confirm i will try to help with your code...

Regards

vamsi

[Former Member · Accepted solution]

Hi Vamsi

Just to clarify it is the transformation below that the routine is for.

datasource----dsoA -

---

dsoB

I will be most grateful for the code, thanks.

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

Hi vamsi

Thanks for your help it worked a treat. Given you maximum points too

[Former Member · Accepted solution]

Hi Vamsi

Just a little problem still with declarations on the code you gave me. Never had problems with declarations in BW 3.5 so this is a little embarrassing.

DATA:

wa_result_package TYPE tys_TG_1, * same type as the result package

When I use the above I get the errror "cannot be converted to the line type of "RESULT_PACKAGE"".

Alternatively when I try

wa_result_package LIKE LINE OF RESULT_PACKAGE,

I get the following error: "No component exists with the name "WA_RESULT_PACKAGE"".

Any ideas what I'm doing wrong.....am i missing something really straightforward here?

Any help will be appreciated. Thanks in advance.