Skip to Content
Technical Articles
Author's profile photo Ivan Milazzotti

Collect using VALUE…FOR GROUPS

Hi!

Well, after a lot of researches I think I’ve found a good (and elegant) solution for my problems using VALUE and FOR GROUPS to create a collect table.

This is my very first contribution to the community and I’m really sorry if you find any error, please feel free to correct and suggest me anything.

I wanted to use the command VALUE, so I’ve tried this:

    TYPES:
      BEGIN OF ty_s_collect,
        carrid     TYPE s_carr_id,
        paymentsum TYPE s_sum,
        seatsmax_b TYPE	s_smax_b,
        seatsocc_b TYPE	s_socc_b,
        seatsmax_f TYPE	s_smax_f,
        seatsocc_f TYPE	s_socc_f,
      END OF ty_s_collect,

      ty_t_collect TYPE SORTED TABLE OF ty_s_collect
                   WITH NON-UNIQUE KEY carrid,

      BEGIN OF ty_s_sum,
        paymentsum TYPE s_sum,
        seatsmax_b TYPE	s_smax_b,
        seatsocc_b TYPE	s_socc_b,
        seatsmax_f TYPE	s_smax_f,
        seatsocc_f TYPE	s_socc_f,
      END OF ty_s_sum.

    DATA:
      tl_collect  TYPE ty_t_collect.

    LOOP AT t_flights ASSIGNING FIELD-SYMBOL(<fs_key>).
      DATA(wl_collect) = CORRESPONDING ty_s_collect( <fs_key> ).
      COLLECT wl_collect INTO tl_collect.
    ENDLOOP.

*******************************************************************
    tl_collect = VALUE ty_t_collect(
                   FOR GROUPS carrier  OF  <fs_flight> IN t_flights
                       INDEX INTO l_tabix
                       GROUP BY ( carrid = <fs_flight>-carrid )
                         LET wl_sum = REDUCE ty_s_sum(
                                        INIT calc TYPE ty_s_sum
                                         FOR r IN GROUP carrier
                                        NEXT calc-paymentsum = calc-paymentsum + r-paymentsum
                                             calc-seatsmax_b = calc-seatsmax_b + r-seatsmax_b
                                             calc-seatsocc_b = calc-seatsocc_b + r-seatsocc_b
                                             calc-seatsmax_f = calc-seatsmax_f + r-seatsmax_f
                                             calc-seatsocc_f = calc-seatsocc_f + r-seatsocc_f )
                         IN
                    ( carrid = carrier-carrid
                      paymentsum = wl_sum-paymentsum
                      seatsmax_b = wl_sum-seatsmax_b
                      seatsocc_b = wl_sum-seatsocc_b
                      seatsmax_f = wl_sum-seatsmax_f
                      seatsocc_f = wl_sum-seatsocc_f ) ).

It works fine, it doesn’t use the REDUCE … FOR GROUP … with nested REDUCE’s for every field I want to calculate.

This is the entire program so you can copy and do some tests:



CLASS lcl_reduce DEFINITION CREATE PUBLIC.

  PUBLIC SECTION.

    METHODS: start.

ENDCLASS.

CLASS lcl_reduce IMPLEMENTATION.

  METHOD start.

    TYPES:
      BEGIN OF ty_s_collect,
        carrid     TYPE s_carr_id,
        paymentsum TYPE s_sum,
        seatsmax_b TYPE	s_smax_b,
        seatsocc_b TYPE	s_socc_b,
        seatsmax_f TYPE	s_smax_f,
        seatsocc_f TYPE	s_socc_f,
      END OF ty_s_collect,

      ty_t_collect TYPE SORTED TABLE OF ty_s_collect
                   WITH NON-UNIQUE KEY carrid,

      BEGIN OF ty_s_sum,
        paymentsum TYPE s_sum,
        seatsmax_b TYPE	s_smax_b,
        seatsocc_b TYPE	s_socc_b,
        seatsmax_f TYPE	s_smax_f,
        seatsocc_f TYPE	s_socc_f,
      END OF ty_s_sum.


    DATA:
      tl_collect  TYPE ty_t_collect,
      lv_sta_time TYPE timestampl,
      lv_end_time TYPE timestampl,
      lv_diff     TYPE p DECIMALS 5.

    SELECT * FROM sflight
             INTO TABLE @DATA(t_flights)
             ORDER BY PRIMARY KEY.

    GET TIME STAMP FIELD lv_sta_time.

    LOOP AT t_flights ASSIGNING FIELD-SYMBOL(<fs_key>).
      DATA(wl_collect) = CORRESPONDING ty_s_collect( <fs_key> ).
      COLLECT wl_collect INTO tl_collect.
    ENDLOOP.

    GET TIME STAMP FIELD lv_end_time.
    lv_diff  = lv_end_time - lv_sta_time.
    WRITE: /(50) 'COLLECT - LOOP GROUP BY...LOOP AT GROUP', lv_diff.

    cl_demo_output=>display( tl_collect ).
    FREE tl_collect.

    GET TIME STAMP FIELD lv_sta_time.

    tl_collect = VALUE ty_t_collect(
                   FOR GROUPS carrier  OF  <fs_flight> IN t_flights
                       INDEX INTO l_tabix
                       GROUP BY ( carrid = <fs_flight>-carrid )
                         LET wl_sum = REDUCE ty_s_sum(
                                        INIT calc TYPE ty_s_sum
                                         FOR r IN GROUP carrier
                                        NEXT calc-paymentsum = calc-paymentsum + r-paymentsum
                                             calc-seatsmax_b = calc-seatsmax_b + r-seatsmax_b
                                             calc-seatsocc_b = calc-seatsocc_b + r-seatsocc_b
                                             calc-seatsmax_f = calc-seatsmax_f + r-seatsmax_f
                                             calc-seatsocc_f = calc-seatsocc_f + r-seatsocc_f )
                         IN
                    ( carrid = carrier-carrid
                      paymentsum = wl_sum-paymentsum
                      seatsmax_b = wl_sum-seatsmax_b
                      seatsocc_b = wl_sum-seatsocc_b
                      seatsmax_f = wl_sum-seatsmax_f
                      seatsocc_f = wl_sum-seatsocc_f ) ).


    GET TIME STAMP FIELD lv_end_time.
    lv_diff  = lv_end_time - lv_sta_time.
    WRITE: /(50) 'COLLECT - FOR GROUPS... REDUCE[FOR IN GROUP]... ', lv_diff.

    cl_demo_output=>display( tl_collect ).

  ENDMETHOD.

ENDCLASS.

START-OF-SELECTION.
  NEW lcl_reduce( )->start( ).

Thank you

I hope you enjoy the code !!

 

Ivan Milazzotti

 

ps: I like to thank Horst Keller, Paul Darcy and Naimesh Patel for all of your documentation, books and posts who helped A LOT !!!

Thank you Sandra Rossi for warning me about the mistake on previous post !!!!

Assigned Tags

      9 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Sandra Rossi
      Sandra Rossi

      Nice initiative. But your code is not representative of a real COLLECT because you group by the primary key of table SFLIGHT so there’s no aggregation at all.

      Here is my proposal, which compares the real COLLECT to one way of doing the aggregation with GROUP BY – REDUCE, the duration is shown in the Unit Test browser (on my laptop 10000 executions last respectively 0.1s versus 0.16s) :

      CLASS lcl_reduce DEFINITION FOR TESTING.
      
        PRIVATE SECTION.
      
          METHODS:
            collect FOR TESTING,
            group_by_reduce FOR TESTING.
      
          METHODS:
            setup.
      
          DATA:
            original_table   TYPE STANDARD TABLE OF sflight,
            expected_collect TYPE STANDARD TABLE OF sflight,
            actual_collect   TYPE STANDARD TABLE OF sflight.
      
      ENDCLASS.
      
      CLASS lcl_itab_loader DEFINITION.
        PUBLIC SECTION.
          CLASS-METHODS load
            IMPORTING source_table TYPE string_table
            EXPORTING itab         TYPE ANY TABLE.
      ENDCLASS.
      
      CLASS lcl_reduce IMPLEMENTATION.
      
        METHOD setup.
      
          lcl_itab_loader=>load(
              EXPORTING source_table = VALUE #(
                ( `CARRID  CONNID  PRICE   PAYMENTSUM  SEATSMAX_B` )
                ( `AA      17      422.94  191334.22   31        ` )
                ( `AA      17      422.94  189984.86   31        ` )
                ( `AA      17      422.94  193482.55   31        ` )
                ( `AZ      555     185.00  0.00        12        ` )
                ( `AZ      555     185.00  5392.75     12        ` ) )
              IMPORTING itab = original_table ).
      
          lcl_itab_loader=>load(
              EXPORTING source_table = VALUE #(
                ( `CARRID  CONNID  PRICE   PAYMENTSUM  SEATSMAX_B` )
                ( `AA      17      1268.82 574801.63   93        ` )
                ( `AZ      555     370.00  5392.75     24        ` ) )
              IMPORTING itab = expected_collect ).
      
        ENDMETHOD.
      
        METHOD collect.
      
          DO 10000 TIMES.
      
            CLEAR actual_collect.
            LOOP AT original_table ASSIGNING FIELD-SYMBOL(<key>).
              COLLECT <key> INTO actual_collect.
            ENDLOOP.
      
            cl_abap_unit_assert=>assert_equals( act = actual_collect exp = expected_collect ).
      
          ENDDO.
      
        ENDMETHOD.
      
        METHOD group_by_reduce.
      
          DO 10000 TIMES.
      
            actual_collect = VALUE #( FOR GROUPS group OF <flight> IN original_table
                                  GROUP BY ( carrid = <flight>-carrid
                                             connid = <flight>-connid )
                                  ( REDUCE #( INIT sum_aux TYPE sflight
                                      FOR line IN GROUP group
                                      NEXT sum_aux = VALUE #( BASE sum_aux
                                         carrid     = line-carrid
                                         connid     = line-connid
                                         price      = sum_aux-price      + line-price
                                         seatsmax   = sum_aux-seatsmax   + line-seatsmax
                                         seatsocc   = sum_aux-seatsocc   + line-seatsocc
                                         paymentsum = sum_aux-paymentsum + line-paymentsum
                                         seatsmax_b = sum_aux-seatsmax_b + line-seatsmax_b
                                         seatsocc_b = sum_aux-seatsocc_b + line-seatsocc_b
                                         seatsmax_f = sum_aux-seatsmax_f + line-seatsmax_f
                                         seatsocc_f = sum_aux-seatsocc_f + line-seatsocc_f ) ) ) ).
      
            cl_abap_unit_assert=>assert_equals( act = actual_collect exp = expected_collect ).
      
          ENDDO.
      
        ENDMETHOD.
      
      ENDCLASS.
      
      CLASS lcl_itab_loader IMPLEMENTATION.
      
        METHOD load.
          DATA: dref_itab_line  TYPE REF TO data,
                itab_line_drefs TYPE STANDARD TABLE OF REF TO data.
      
          " Create line of internal table to be loaded
          CREATE DATA dref_itab_line LIKE LINE OF itab.
          ASSIGN dref_itab_line->* TO FIELD-SYMBOL(<itab_line>).
      
          " First line = list of component names
          ASSIGN source_table[ 1 ] TO FIELD-SYMBOL(<source_first_line>).
          FIND ALL OCCURRENCES OF REGEX '[^ ]+([ ]+|$)' IN <source_first_line> RESULTS DATA(matches).
          LOOP AT matches ASSIGNING FIELD-SYMBOL(<match>).
            DATA(comp_name) = <source_first_line>+<match>-offset(<match>-length).
            ASSIGN COMPONENT comp_name OF STRUCTURE <itab_line> TO FIELD-SYMBOL(<itab_line_comp>).
            APPEND REF #( <itab_line_comp> ) TO itab_line_drefs.
          ENDLOOP.
      
          " Next lines = data
          LOOP AT source_table FROM 2 ASSIGNING FIELD-SYMBOL(<source_line>).
            CLEAR <itab_line>.
            LOOP AT matches ASSIGNING <match>.
              READ TABLE itab_line_drefs INDEX sy-tabix INTO DATA(itab_line_dref).
              ASSIGN itab_line_dref->* TO <itab_line_comp>.
              <itab_line_comp> = <source_line>+<match>-offset(<match>-length).
            ENDLOOP.
            INSERT <itab_line> INTO TABLE itab.
          ENDLOOP.
      
        ENDMETHOD.
      
      ENDCLASS.
      Author's profile photo Ivan Milazzotti
      Ivan Milazzotti
      Blog Post Author

      Thank you Sandra Rossi! I've just review the post !!

      Author's profile photo Sandra Rossi
      Sandra Rossi

      Maybe I was not clear, or I don't understand what you're trying to achieve, but your code still reads 94 lines from SFLIGHT (in my system), and the result is again 94 lines in TL_COLLECT, but I expect something like 20 lines in TL_COLLECT because there are only 20 groups of distinct values for CARRID and CONNID. And there is no sum if I see well. Could you tell me where I'm wrong in my understanding? Thanks.

      Author's profile photo Ivan Milazzotti
      Ivan Milazzotti
      Blog Post Author

      Sorry Sandra, I did few updates in this post after you correct me, can you please try again?

      Author's profile photo Sandra Rossi
      Sandra Rossi

      It's okay now, thank you ! 🙂

      Author's profile photo Ivan Milazzotti
      Ivan Milazzotti
      Blog Post Author

      Thank YOU !!!!

      Author's profile photo Suhas Saha
      Suhas Saha

      Hi Ivan,

      I hope your exercise/research was for pure academic purposes. I wouldn’t like to see the GROUP BY…REDUCE in productive code.

      Just from a clean code perspective COLLECT looks much clean & compact. It is far more easy to understand as well.

      You could do away with the helper work area WL_COLLECT if it bothers you ?

      loop at T_FLIGHTS assigning field-symbol(<FS_KEY>).
        collect corresponding TY_S_COLLECT( <FS_KEY> ) into TL_COLLECT.
      endloop.

      BR,

      Suhas

      Author's profile photo Ivan Milazzotti
      Ivan Milazzotti
      Blog Post Author

      Suhas, the idea here is just to show another way to use new VALUE command. The traditional way using COLLECT stills better. I wanted to show how flexible the commands are.

      You could do the following

          SELECT DISTINCT
                 carrid,
                 SUM( paymentsum ),
                 SUM( seatsmax_b ),
                 SUM( seatsocc_b ),
                 SUM( seatsmax_f ),
                 SUM( seatsocc_f )
            FROM sflight
            INTO TABLE @tl_collect
            GROUP BY carrid
            ORDER BY carrid.
      

      if you want it, it's your choice !!

       

      Thank you!

      Ivan

      Author's profile photo Wagner Rogerio Leva
      Wagner Rogerio Leva

      Your sharing was great help for me. Thank you Ivan!

      King regards.