topic Summing similar pattern values row wise in Application Development and Automation Discussions

Summing similar pattern values row wise

Former Member — Fri, 24 Jan 2014 11:15:27 GMT

Hi,

Pls find below my simple requirement.

Below is the data table.

Customer number( key field)	vv001	vv002	vv003	vv004
Mark	300	100	20	10
John	500	20	400	300

vv001 - vv004 are cost values.

Is there any way to sum cost fields of a particular customer using SELECT query directly.

I know , SUM keyword does it column wise , I want it rowwise.

I want the result for each customer in different variables, i.e. Mark = 430 , John = 1220

Also, in my real requirement I have about 30 such cost fields ranging from vv001 to vv030. So is there any way to use the pattern 'vv*' instead of writing all the 30 fields separately!!

Thanks in advance.

Re: Summing similar pattern values row wise

Former Member — Fri, 24 Jan 2014 11:23:05 GMT

First of all why are you using aggregate Functions, Example SUM in SELECT query, which will Drastically hit your performance....... Why can't you get all your entries in an Internal table then use Control level statements such as AT NEW and AT LAST to do your Calculation which would be a better choice....

Good luck...

Re: Summing similar pattern values row wise

Former Member — Fri, 24 Jan 2014 11:28:38 GMT

Hi,

Yes You can use the Aggregate function SUM for each customer..you will get the customer with the total value.

SUM( [DISTINCT] col ) Determines the sum of the content of the column col in the resulting set or in the current group. The data type of the column has to be numerical.

Re: Summing similar pattern values row wise

Former Member — Fri, 24 Jan 2014 11:41:00 GMT

Hi Arun ,

Thanks for the quick info.

Yes indeed using control level stmts was the other way in my mind. Was not aware of performance limitations.

So, do you think there is any way to use a pattern for all the 30 fields instead of writing each of them separately. All fields are of domain WERTV8 (currency field).

Thanks.

Re: Summing similar pattern values row wise

Former Member — Fri, 24 Jan 2014 11:43:51 GMT

Hi Kiran,

Did u mean something like this : SUM ( VV001 ) ?

That would give me sum column-wise right? Not row-wise.

Correct me If I am wrong.

Thanks

Re: Summing similar pattern values row wise

Tomas_Buryanek — Fri, 24 Jan 2014 12:11:16 GMT

This summing will have to be done programmatically.

But you can help yourself by doing it dynamic though.

SELECT (columns) ...

and after it

ASSIGN COMPONENT id/name OF STRUCTURE ...

Re: Summing similar pattern values row wise

Former Member — Fri, 24 Jan 2014 15:49:09 GMT

Hi,

DATA: ld_vv001 type vv001,

lv_sum type p decimals 2.

SELECT (columns)

DO n TIMES VARYING ld_vv001 FROM vv001 NEXT vv02.

IF not ld_vv001 IS INITIAL.

lv_sum = lv_sum + ld_vv001.

ENDIF.

ENDDO.

This is how system calculates actual cost in Costing/productionplanning

Regards,

DPM

Re: Summing similar pattern values row wise

Former Member — Mon, 27 Jan 2014 09:34:19 GMT

Hi Debopriyo,

Thanks a lot!! It helped me a great deal

One more thing. We are asked to use work areas and internal tables wherever necessary instead of direct tables. And also other performance considerations like selecting only useful fields in select query. So I had to code the logic like below.

TYPES: BEGIN OF TY_vv,
      vv001 TYPE CE1BR01-vv001,
      vv002 TYPE CE1BR01-vv002,
      vv003 type CE1BR01-vv003,
      vv004 type CE1BR01-vv004,
      vv005 type CE1BR01-vv005,
      vv006 type CE1BR01-vv006,
      vv007 type CE1BR01-vv007,
      vv008 type CE1BR01-vv008,
      vv009 type CE1BR01-vv009,
      vv010 type CE1BR01-vv010,
      END OF TY_vv.

data: lwa_vv type ty_vv.

select single vv001 vv002 vv003 vv004 vv005
              vv006 vv007 vv008 vv009 vv010
from ce1br01 into lwa_vv
where PALEDGer = '01' and
vrgar = 'B'
and perio = '2014009'.

if sy-subrc = 0.
DO 10 TIMES VARYING G_VV FROM lwa_vv-VV001 NEXT lwa_vv-VV002.
G_SUM_VV = G_SUM_VV + G_VV.
ENDDO.
write : 'Sum is : ' , g_sum_vv.
else.
write 'no data found'.
endif.

This is working fine.

My ques is, can you suggest some other way of specifying these large number of fields. I believe, this is not the best way to do it. And I have 100 such fields in my real requirement.

I could have coded like below. But, have to make use of work areas as above.

CLEAR: G_SUM_VV, G_VV.

select single * from ce1br01
where PALEDGer = '01' and
vrgar = 'B'
and perio = '2014009'.
DO 10 TIMES VARYING G_VV FROM CE1BR01-VV001 NEXT CE1BR01-VV002.
G_SUM_VV = G_SUM_VV + G_VV.
ENDDO.
WRITE : 'New sum : ', G_SUM_VV.

Please help if there is any other better way to specify those 100 fields.

Thanks.

Re: Summing similar pattern values row wise

anup_deshmukh4 — Mon, 27 Jan 2014 09:44:01 GMT

Hello Barkha,

You can have a select ....endselect. option too.... .

SELECT <YOUR _FIELDS> INTO <WORK_AREA> WHERE <YOUR_CONDITION>.

" Your calculation may be with an additional field in your work area based on your conditions.

<WORK_AREA>-ROW_TOTAL = <WORK_AREA>-FIELD1 + <WORK_AREA>-FIELD2....<WORK_AREA>-FIELDN

APPEND <YOUR_WORK_AREA> TO <INTERNAL_TABLE>

ENDSELECT.

hope this helps,

Anup D.

Re: Summing similar pattern values row wise

Tomas_Buryanek — Mon, 27 Jan 2014 10:11:33 GMT

Please read my previous response

You can create structure dynamicaly using RTTS (there are many tutorials, topics etc...).

After it you can SELECT fields dynamicaly as I already wote: SELECT (fields) - where "fields" is dynamicaly created list of fields you want select.

And at the end, you can SUM quantities with dynamic assigning:

ASSIGN COMPONENT id/name OF STRUCTURE ...

Hint:

DATA: id(3) type n.

DO 100 times.

id = id + 1.

fieldname = 'vv' && id.

ENDDO.

Re: Summing similar pattern values row wise

former_member585060 — Mon, 27 Jan 2014 10:12:17 GMT

Hi,

Try below

DATA : v_field TYPE c LENGTH 5,
v_inc TYPE n LENGTH 3.

CONSTANTS : c_vw TYPE c LENGTH 9 VALUE 'LWA_VV-VW'.

FIELD-SYMBOLS : <fs_field> TYPE ANY.

SELECT SINGLE *
                FROM ce1br01
                INTO lwa_vv
                WHERE paledger = '01'
                AND   vrgar = 'B'
                AND   perio = '2014009'.

DO.
v_inc = sy-index.
CONCATENATE c_vw v_inc INTO v_field.

ASSIGN (v_field) TO <fs_field>.
IF sy-subrc = 0.
v_total = v_total + <fs_field>.
ELSE.
EXIT.
ENDIF.
ENDDO.

Thanks & Regards

Bala Krishna

Re: Summing similar pattern values row wise

Former Member — Mon, 27 Jan 2014 14:31:51 GMT

Hi,

You can create dynamic type with the relevant fields. Check the following thread:

Dynamic Internal Table Creation using RTTS

Easier way out is to create a structure in SE11 with the fields you need and refer that structure in your report.

Regards,

DPM

Re: Summing similar pattern values row wise

Former Member — Wed, 29 Jan 2014 04:25:44 GMT

Hi Tomas,

Sorry for the late reply!

RTTS proved to be very useful for my requirement. Thanks so much

However, currently I am working with select * itself and will implement dynamic concept at a later stage, if required!

Will get back then and post queries if any

Thanks once again!