04-22-2006 7:27 AM
Dear Friends,
We are having two ztables. One table which is quantity table is hving fields Co Code, Bus Area, Item & Quantity. In this table mainly Items are of 4 types. In the other ztable which is rates table we are having fields like Year, Product 1, Product 2, Product 3, Product4 , Product 5, Product 6 in which we are maintaing rates.
Now, we want if the Itam is like Product 1 then it should take the quantity from the table 1 for that particular combination of Co Code & Bus Area then multiply it with the unit rate given in othr table.
Plz guide me in this in writing the code.
Thanx
nishu
04-22-2006 12:27 PM
Hi,
i have send u the code please reply back ; i would like know ; what was the outcome.
Reagards
Manoj
04-22-2006 7:34 AM
hi Nishu ,
please calrify little more so i can give u working code.
it is clear about table 1
but table 2 is not clear
what is common field bt 2 tables & even year is conflicting
as per my understanding code should be like this
read the rate table ( table2) in to the workarea( structure called rate ) based on year u want.
<b>value</b> is variable for the cost
loop at table1 ( co , bus , item , quntity )
case table1-item.
when 'Product1'.
<b> value = table1-quantity * rate-product1.</b>
when 'Product2'.
<b>value = table1-quantity * rate-product2.</b>
when 'Product3'.
<b> value = table1-quantity * rate-product3.</b>
when 'Product4'.
<b> value = table1-quantity * rate-product4.</b>
when 'Product5'.
<b> value = table1-quantity * rate-product5.</b>
endcase.
u can store co, bus , item , value into new table.
append into new table.
Endloop.
i have taken item as the column name ; understanding form ur post.
Mark helpful answers
Any question is most welcome
Message was edited by: Manoj Gupta
04-22-2006 8:43 AM
Hi Manoj,
Actually we are having two tables: one in which we are maintaing total quantity based on the combinations of Co Code, Bus Area & Products, like: example-
CCODE B AREA ITEM QTY
0100 F141 P1 110
0100 F142 P1 2
0111 F111 P1 5
In the second table we are maintaing unit rates for the products which will b yearly maintined, like: example-
Year P1 P2 P3 P4 P5
2006 100 120 123 124 125
2007 90 10 243 34 35
So, now we want the the total expense, means for a particular combination of Co Code, Bus Area & Item what is the total expense.
I hope all inputs I have furnished, if any other thing I have to clarify plz tell.
Thanks,
nishu
04-22-2006 7:41 AM
Hi,
select single quantity into variable1 from table1 where C0code = ' ' and BusArea = ' '.
This will get the quantity into some variable1.
To get the rate for a product 1, we will need year to access the table.
You havent mentioned anything about the year.
select single product1 into rate from table2 where year =' '.
your value = variable1 * rate.
Regards,
Tanveer.
Please mark helpful answers.
04-22-2006 7:51 AM
they manoj, You have ade a mistake,
It should be
table1-quantity * rate-product1 and not
table1-quantity = rate-product1.
Please correct that.
Regards,
Tanveer.
04-22-2006 8:11 AM
Hi,
How do we identify the product type, is there a seperate field like 'MTART' for material type which holds the value.
Or is it as manoj has written the value of material itself will be 'PRODUCT1', PRODUCT2 etc.
What is the use of field YEAR in second ztable.
Can you provide us some more info..
04-22-2006 8:50 AM
Hi Nishu,
item name present in one table1 is same as column name in the table 2 . this is the inference which i can get it.
1) do u want to calulate for a single item or multiple item
2) what exactly is the significant of the year in the table 2.
3) do u want year specific price for item .
Please Clarify
can i have ur email address for faster communication
Regards
Manoj B Gupta
manoj.baijnath@wipro.com
Pune
Message was edited by: Manoj Gupta
04-22-2006 8:54 AM
Hi, Do u need to calclate it for all the years for product P1.
In that case,
select single quantity into quan from table1 where C0code = '0100 ' and BusArea = 'f141 ' and item = 'P1'.
This will get the quantity into some quan.
To get the rate for a product1 for all years,
Data: rate type table of i occurs 0.
select product1 into table rate from table2 where year =' '.
loop at rate.
value = value + ( quan * rate ).
endloop.
Value will contain for p1 = (110100) + (11090)
Regards,
Tanveer.
Please mark helpful answers.
04-22-2006 9:11 AM
If this is the case
What i think solution should be
data : item_tab type table of table_item,
wa_item type table_item,
price_tab type table of table_price,
wa_price type table_price.
Total_expenses = 0.
*----
select particular item based on the combination
select single * from table_item into wa_item
where CCODE = <Parameter_CCODE>
and BAREA = <Parameter_BAREA>
and ITEM = <Parameter_item>.
if sy-subrc = 0.
*----
prices
select * from table_price into price_tab.
if sy-subrc = 0.
*----
Calculatre total expenses for the items
loop at price_tab into wa_price.
case <Parameter_item>.
when 'P1'.
Total_expenses = Total_expenses + ( wa_item-qty * wa_price-p1 ).
when 'P2'.
Total_expenses = Total_expenses + ( wa_item-qty * wa_price-p2 ).
when 'P3'.
Total_expenses = Total_expenses + ( wa_item-qty * wa_price-p3 ).
when 'P4'.
Total_expenses = Total_expenses + ( wa_item-qty * wa_price-p4 ).
endloop.
endif.
endif.
if total_expenses = 0.
no item was select for the given combination
endif.
U will have the total expenses for all the yaer of a particular item.
Regards
Manoj
Message was edited by: Manoj Gupta
04-22-2006 9:20 AM
Hi manoj,
I dont think u need to use
select * from table_price into price_tab.
This would have high space and time complexity in case if we are talking of only one product.
There is no need to select the entire table then.
You could select only <Parameter_item> in this case as thats what you have used for previous select statement.
Have a look at my previous relpy.
Regards,
Tanveer.
04-22-2006 10:20 AM
u can try something like this.
select companycode busarea item qty from ztable1
into table t_ztable1 where <some condition>.
loop at t_ztable1.
refresh t_quan.
select (t_ztable1-item) from ztable2
into table t_quan.
do the rate calculation.
endloop.
04-22-2006 12:27 PM
Hi,
i have send u the code please reply back ; i would like know ; what was the outcome.
Reagards
Manoj
04-24-2006 7:26 AM
Hi Nishu,
You can execute this code.
REPORT zkun_sdntable .
TABLES: zkun_quanttab, zkun_ratetab.
DATA: BEGIN OF itab1 OCCURS 0.
INCLUDE STRUCTURE zkun_quanttab.
DATA: END OF itab1.
DATA: BEGIN OF itab2 OCCURS 0.
INCLUDE STRUCTURE zkun_ratetab.
DATA: END OF itab2.
DATA : val TYPE zkun_quanttab-my_qty,
rate TYPE zkun_ratetab-p1.
DATA : BEGIN OF itab3 OCCURS 0,
value TYPE zkun_quanttab-my_qty,
my_ccode TYPE zkun_quanttab-my_ccode,
my_barea TYPE zkun_quanttab-my_barea,
my_item TYPE zkun_quanttab-my_item,
my_year TYPE zkun_ratetab-my_year,
END OF itab3.
SELECT * FROM zkun_quanttab INTO TABLE itab1.
SELECT * FROM zkun_ratetab INTO TABLE itab2.
val = 0.
rate = 0.
LOOP AT itab1.
LOOP AT itab2.
CASE itab1-my_item.
WHEN 'P1'.
rate = itab2-p1.
WHEN 'P2'.
rate = itab2-p2.
ENDCASE.
val = val + rate * itab1-my_qty.
ENDLOOP.
itab3-value = val.
itab3-my_ccode = itab1-my_ccode.
itab3-my_barea = itab1-my_barea.
itab3-my_item = itab1-my_item.
itab3-my_year = itab2-my_year.
APPEND itab3.
val = 0.
ENDLOOP.
LOOP AT itab3.
WRITE : / itab3-my_ccode, itab3-my_barea, itab3-my_year , itab3-my_item,
itab3-value.
ENDLOOP.
Hope this solves your query.
Regards,
Kunal.
Note :
The structure of table <b>zkun_quanttab</b> contains
my_ccode for company code, my_barea for business area, my_item for item and my_qty for quantity.
The structure of table <b>zkun_ratetab</b> contains
my_year for year, p1 for product1, p2 for product2 etc.