topic Re: GROUP BY LOOP in Application Development and Automation Discussions

GROUP BY LOOP

former_member789218 — Wed, 09 Feb 2022 20:07:43 GMT

Hi, i want to ask a question about the LOOP statement.

I have an itab from which i want to extract only country, fum and nfum into another itab, let's suppose itab_alv2.

How can i extract them to this last table grouping them by country making the sum of the fields fum and nfum.

EXAMPLE INPUT TABLE:

COUNTRY FUM NFUM

AA 0 1

IT 1 0

IT 0 1

OUTPUT TABLE:

COUNTRY FUM NFUM

AA 0 2

IT 1 1

REPORT zesercizio_9.<br><br>TABLES: sbook,<br>        scustom.<br><br>TYPES: BEGIN OF ty_tab,<br>         country  TYPE scustom-country,<br>         carrid   TYPE sbook-carrid,<br>         connid   TYPE sbook-connid,<br>         fldate   TYPE sbook-fldate,<br>         bookid   TYPE sbook-bookid,<br>         customid TYPE sbook-customid,<br>         smoker   TYPE sbook-smoker,<br>         id       TYPE scustom-id,<br>         name     TYPE scustom-name,<br>         fum      TYPE i,<br>         nfum     TYPE i,<br>       END OF ty_tab.<br><br>TYPES:BEGIN OF ty_alv,<br>        country TYPE scustom-country,<br>        fum     TYPE i,<br>        nfum    TYPE i,<br>      END OF ty_alv.<br><br>TYPES:BEGIN OF ty_alv2,<br>        country TYPE scustom-country,<br>        fum     TYPE i,<br>        nfum    TYPE i,<br>      END OF ty_alv2.<br><br>DATA: it_alv TYPE TABLE OF ty_alv,<br>      wa_alv TYPE ty_alv.<br><br>DATA: counter_fumatori     TYPE i,<br>      counter_non_fumatori TYPE i.<br><br>DATA: it_alv2 TYPE TABLE OF ty_alv2,<br>      wa_alv2 TYPE ty_alv2.<br><br>DATA: it_tab TYPE TABLE OF ty_tab,<br>      wa_tab TYPE ty_tab.<br><br>DATA: fum  TYPE i,<br>      nfum TYPE i.<br><br>DATA: totale_fumatori     TYPE i,<br>      totale_non_fumatori TYPE i.<br><br>DATA: new_count TYPE c.<br><br>new_count = ' '.<br><br>PARAMETERS: p_carrid LIKE sbook-carrid,<br>            p_connid LIKE sbook-connid,<br>            p_fldate LIKE sbook-fldate.<br><br>SELECT  a~carrid,<br>        a~connid,<br>        a~fldate,<br>        a~bookid,<br>        a~customid,<br>        a~smoker,<br>        b~id,<br>        b~name,<br>        b~country<br>  FROM sbook AS a<br>  INNER JOIN scustom AS b<br>  ON a~customid EQ b~id<br>  WHERE a~carrid EQ @p_carrid<br>  AND   a~connid EQ @p_connid<br>  AND   a~fldate EQ @p_fldate<br>  INTO CORRESPONDING FIELDS OF TABLE @it_tab.<br><br>SORT it_tab BY country.<br><br>LOOP<br>   AT it_tab INTO wa_tab.<br><br>  IF wa_tab-smoker EQ 'X'.<br><br>    wa_tab-fum = 1.<br>    wa_tab-nfum = 0.<br><br>  ELSE.<br><br>    wa_tab-fum = 0.<br>    wa_tab-nfum = 1.<br><br>  ENDIF.<br><br>  MODIFY it_tab FROM wa_tab.<br>  CLEAR wa_tab.<br><br>ENDLOOP.<br><br>LOOP AT it_tab INTO wa_tab GROUP BY wa_tab-country.<br><br>  wa_alv2-country = wa_tab-country.<br>  wa_alv2-fum = wa_tab-fum.<br>  wa_alv2-nfum = wa_tab-nfum.<br><br>  APPEND wa_alv2 TO it_alv2.<br>  CLEAR wa_tab.<br>  CLEAR wa_alv2.<br><br>ENDLOOP.

Re: GROUP BY LOOP

jerryjanda — Wed, 09 Feb 2022 20:11:05 GMT

Hi, Robert:

Thank you for visiting SAP Community to get answers to your questions. Since you're asking a question here for the first time, I recommend that you familiarize yourself with https://community.sap.com/resources/questions-and-answers, as it provides tips for preparing questions that draw responses from our members. Feel free to take our Q&A tutorial at https://developers.sap.com/tutorials/community-qa.html as well, as that will help you when submitting questions to the community.

Examples of how you can improve a question:

* Outline what steps you took to find answers (and why they weren't helpful).

* Share screenshots of what you've seen/done.

* Use the "insert code" feature properly to help members understand your issue.

* Make sure you've applied the appropriate tags.

If you don't apply the correct tags, the right experts won't see your question to answer it.

Should you wish, you can revise your question by selecting Actions, then Edit.

The more details you provide, the more likely it is that members will be able to respond.

I also recommend that you include a profile picture. By personalizing your profile, you encourage readers to respond: https://developers.sap.com/tutorials/community-profile.html.

Kind regards,

--Jerry

Moderation Lead

Re: GROUP BY LOOP

ascm — Thu, 10 Feb 2022 10:24:59 GMT

One possibly way:

types:
  begin of ts_output,
    group_name type sbook-carrid,
    amount     type sbook-loccuram,
  end of ts_output.
types tt_output type standard table of ts_output with empty key.


select *
       from sbook
       into table @data(sbook_entries).

data(summaries) = value tt_output( for groups group_name of <group> in sbook_entries
                       where ( carrid is not initial )
                       group by ( carrid = <group>-carrid )
                         ( group_name = group_name
                           amount = reduce #( init i = conv s_l_cur_pr( 0 )
                                              for <single_amount> in sbook_entries
                                              where ( carrid = group_name )
                                              next i = i + <single_amount>-loccuram ) ) ).

try.
    cl_salv_table=>factory( exporting list_display   = if_salv_c_bool_sap=>false
                            importing r_salv_table   = data(salv)
                            changing t_table        =  summaries ).
    salv->get_functions( )->set_all( ).
    salv->display( ).
  catch cx_salv_msg cx_salv_not_found.
endtry.

Re: GROUP BY LOOP

former_member789218 — Thu, 10 Feb 2022 11:13:42 GMT

Thank you for the support.

Re: GROUP BY LOOP

Sandra_Rossi — Thu, 10 Feb 2022 20:58:48 GMT

Please edit your question, code is not shown correctly. Future visitors might be willing understand the question before looking at the answer.