Technical Articles

Ivan Milazzotti

November 21, 2018 3 minute read

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 !!!!

9 Comments

You must be Logged on to comment or reply to a post.

Sandra Rossi

November 21, 2018 at 2:24 pm

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.

Ivan Milazzotti

Blog Post Author

November 21, 2018 at 3:31 pm

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

Sandra Rossi

November 21, 2018 at 3:45 pm

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.

Ivan Milazzotti

Blog Post Author

November 21, 2018 at 3:59 pm

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

Sandra Rossi

November 21, 2018 at 4:59 pm

It's okay now, thank you ! 🙂

Ivan Milazzotti

Blog Post Author

November 21, 2018 at 5:01 pm

Thank YOU !!!!

Suhas Saha

November 22, 2018 at 1:55 pm

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

Ivan Milazzotti

Blog Post Author

November 22, 2018 at 3:44 pm

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

Wagner Rogerio Leva

January 21, 2019 at 2:20 pm

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

King regards.

Technical Articles

Collect using VALUE…FOR GROUPS

Assigned Tags