topic Re: Calculation within records in Application Development and Automation Discussions

Calculation within records

Former Member — Thu, 28 Aug 2014 04:56:38 GMT

Hi All,

I have data for Material,Week and quantity contained in internal table as below. I have a forth column which is blank in internal table. I have to calculate average value in the column based upon all values for same material considering all weeks remaining below that row. Say if Material M1 has 10 weeks data then column "Calculated Column" in first row should show "Sum of 10 weeks quantity/10", in second row "Sum of 9 weeks/9" and so on.

Material	Week	Quantity	Calculated Column
M1	1	10	(10+11+12)/3
M1	2	11	(11+12)/2
M1	3	12	12
M2	1	20	(20+21+22)/3
M2	2	21	(21+22)/3
M2	3	22	22

Could you please give me some better ideas to handle this in the loop?

Cheers,

-Su

Re: Calculation within records

davis_raja — Thu, 28 Aug 2014 05:52:35 GMT

Dear Kaur,

Sort your internal table by material ascending and week descending.

Then have a count of the week for every loop and have a local variable to add up the quantity and calculate the value.

Try the code provided below.

SORT it_tab BY zmatnr ASCENDING zweek DESCENDING.

LOOP AT it_tab INTO wa_tab.

   AT NEW zmatnr.
     CLEAR : v_week_count, v_qty.
   ENDAT.

   v_week_count = v_week_count + 1.
   v_qty        = v_qty + wa_tab-zqty.

   wa_tab-zcalc_col = v_qty / v_week_count.

   MODIFY it_tab FROM wa_tab TRANSPORTING zcalc_col.

ENDLOOP.

Re: Calculation within records

VenkatRamesh_V — Thu, 28 Aug 2014 07:33:10 GMT

Hi Kaur,

Hope it helpful.

DATA: BEGIN OF itab OCCURS 0,
       material(02) TYPE c,
       week TYPE i,
       qty TYPE i,
       cal TYPE i,
       flag(01) TYPE c,
       END OF itab,

       itab1 LIKE itab OCCURS 0 WITH HEADER LINE ,
       lv_qty TYPE i,
       wa LIKE LINE OF itab,
       lv_week TYPE i.

FIELD-SYMBOLS: <wa> like LINE OF itab,
                <wa1> like LINE OF itab.

START-OF-SELECTION.

itab-material = 'M1'.
itab-week     = '1'.
itab-qty      = '10'. APPEND itab. CLEAR itab.

itab-material = 'M1'.
itab-week     = '2'.
itab-qty      = '11'. APPEND itab. CLEAR itab.

itab-material = 'M1'.
itab-week     = '3'.
itab-qty      = '12'. APPEND itab. CLEAR itab.

itab-material = 'M2'.
itab-week     = '1'.
itab-qty      = '20'. APPEND itab. CLEAR itab.

itab-material = 'M2'.
itab-week     = '2'.
itab-qty      = '21'. APPEND itab. CLEAR itab.

itab-material = 'M2'.
itab-week     = '3'.
itab-qty      = '22'. APPEND itab. CLEAR itab.

sort itab by material week ASCENDING.
itab1[] = itab[].

loop at itab ASSIGNING <wa>.

READ TABLE itab1 ASSIGNING <wa1> WITH KEY material = <wa>-material
                                             qty      = <wa>-qty.

loop at itab1 WHERE material = <wa>-material.
CHECK itab1-flag IS INITIAL.
lv_qty = lv_qty + itab1-qty.
lv_week = lv_week + 1.

endloop.

if <wa1> IS NOT INITIAL.
<wa1>-flag = 'X'.
endif.

<wa>-cal = lv_qty / lv_week.
CLEAR: lv_qty, lv_week.
endloop.

loop at itab.

WRITE:/(02) itab-material,
        (04) itab-week,
        (08) itab-qty,
        (12) itab-cal.

ENDLOOP.

Try to avoid nested loop use parallel cursor in case of performance issue.

Regards,

Venkat.

Re: Calculation within records

SuhaSaha — Thu, 28 Aug 2014 08:03:27 GMT

DATA: BEGIN OF itab OCCURS 0,
       material(02) TYPE c,
       week TYPE i,
       qty  TYPE i,
       cal  TYPE i,
       flag(01) TYPE c,
       END OF itab,

Do not use internal tables with header lines.




Try to avoid nested loop use parallel cursor in case of performance issue.

Have you heard about SORTED/HASHED tables?

I have lost count of how many times i have advised you to cross-check your responses before posting. And this is not fun anymore.

- Suhas

Re: Calculation within records

VenkatRamesh_V — Thu, 28 Aug 2014 08:34:06 GMT

Dear Suhas,

First of all thank you for replying me,

I heard about Sorted/ Hashed tables without using header line in internal table.

but I given some sample code with a user specified data to get the desired output with Some logic,

User insisted on Idea ?. so I done some sample code In case of time consumption.

Because of your advice i tested the output, but i missed Coding efficiency, Apologize me it will never happen again.

Regards,

Venkat.

Re: Calculation within records

davis_raja — Thu, 28 Aug 2014 09:23:47 GMT

I doubt that your code will work flawlessly.

READ TABLE will fail if 2 or more weeks (records) have same material number and Quantity.

Re: Calculation within records

SuhaSaha — Thu, 28 Aug 2014 09:48:32 GMT


User insisted on Idea ?. so I done some sample code In case of time consumption.

This is no excuse for posting wrong content.

It is not about posting response ASAP. If you don't have time to write a code-snippet then do not write one.

Re: Calculation within records

Former Member — Thu, 28 Aug 2014 10:30:42 GMT

Hi,

try something like that:

LOOP AT itab INTO wa.

CLEAR h_sum.

CLEAR h_count.

LOOP AT itab INTO wa2

WHERE material EQ wa-material

AND week GE wa-week.

ADD 1 TO h_count.

ADD wa2-quantity TO h_sum.

ENDLOOP.

wa-calculated = h_sum / h_count.

MODIFY itab FROM wa.

ENDLOOP.

Regards,

Klaus