Loop at Group by with reference field symbol challenge

[sap_cohort]

Is it possible to use "loop at group" by with a table field symbol that was dereferenced from a dynamic table reference?

FIELD-SYMBOLS <lt_data> TYPE STANDARD TABLE.
FIELD-SYMBOLS <ls_data> TYPE any.

ASSIGN ir_data->* TO <lt_data>.
CREATE DATA lo_data LIKE LINE OF <lt_data>.
ASSIGN lo_data->* TO <ls_data>.

LOOP AT <lt_data> INTO <ls_data> GROUP BY ( type = <ls_data>-kurst ).
(type = doesn't work because <ls_data> has no structure and does not have component kurst)

[sap_cohort]

Would I be on the right track with
LOOP AT <lt_data> INTO <ls_data> GROUP BY ( type = '<ls_data>-kurst' ).

[loyd_enochs3]

The GROUP BY clause is used in SQL, not internal table processing.

Use COLLECT for internal tables.

[maheshpalavalli]

Hi Loyd,

Now it works with Internal table as well.

https://help.sap.com/doc/abapdocu_751_index_htm/7.51/en-us/abaploop_at_itab_group_by.htm

SAP sure is changing a lot of things in ABAP,, which is good and making it interesting.

What is more interesting is that we can now write select queries on ABAP internal table 😄 😄

https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abapselect_itab.htm

Br,

Mahesh

[loyd_enochs3]

Mahesh, I am on a much older version and don't have all those slick tools to play with 🙂

[sap_cohort]

Hi Loyd, I have used group by before and it works great. Just not sure how to use it with all this dynamic reference code yet. . GROUP BY It is part of newer ABAP.
http://zevolving.com/2015/10/abap-740-loop-at-with-group-by/

So far I have below. Not sure if it works yet.

LOOP AT <lt_data> INTO <ls_data> 
     GROUP BY ( type   = '<ls_data>-kurst'
                exdate = '<ls_data>-gdatu'
                srccur = '<ls_data>-fcurr'
                tgtcur = '<ls_data>-tcurr') ASCENDING REFERENCE INTO DATA(dimension_group).

[GK817]

Please use 'comment' to respond to answers..

[loyd_enochs3]

Kenneth, this old system explains why it doesn't work for me 🙂

[GK817]

Hi kenneth.murray2 ,

I don't think you can use GROUP BY for a generic type of table. You have to define table structure before using it for GROUP BY.

This is my understanding. Let's see if it is possible. Waiting for more experts to respond.

Regards

GK

[sap_cohort]

I'm hoping

LOOP AT <lt_data> INTO <ls_data> GROUP BY ( type = '<ls_data>-kurst' ).

will work. testing shortly.

[GK817]

Let us know if it worked.

[srikanthnalluri]

'<ls_data>-kurst' will be treated as character and condition will check against it. So i don't think it will work.

[Sandra_Rossi]

I agree, the documentation says that it's not possible, cf my comment below Kenneth answer (where he provides a code which "is working correctly")

[sap_cohort]

Hi Team ABAP,
It appears that this is working correctly!

SAP_ABA 75A 0013 SAPK-75A13INSAPABA Cross-Application Component
Very powerful! Challenge learning the syntax with reference variables!

FIELD-SYMBOLS <lt_data> TYPE STANDARD TABLE.
FIELD-SYMBOLS <ls_data> TYPE any.
ASSIGN ir_data->* TO <lt_data>.
CREATE DATA lo_data LIKE LINE OF <lt_data>.
ASSIGN lo_data->* TO <ls_data>.<br>
LOOP AT <lt_data> INTO <ls_data>
     GROUP BY ( type   = '<ls_data>-kurst'
                exdate = '<ls_data>-gdatu'
                srccur = '<ls_data>-fcurr'
                tgtcur = '<ls_data>-tcurr') ASCENDING REFERENCE INTO DATA(dimension_group).

[srikanthnalluri]

@Kenneth Murray Thanks for the information

However I tried pretty much same example and it is not working for me, am i doing something wrong. please let me know.

types:
  begin of ty_mara,
    matnr type matnr,
    mtart type mtart,
    mbrsh type mbrsh,
    matkl type matkl,
  end of ty_mara.

data:
  lt_mara type standard table of ty_mara,
  lw_mara type ty_mara.

data:
  r_type_struct type ref to cl_abap_structdescr,
  r_type_table  type ref to cl_abap_tabledescr,
  lt_comp_table type cl_abap_structdescr=>component_table,
  lw_comp_table type abap_componentdescr.
data:
  r_data_tab type ref to data,
  r_data_str type ref to data.

field-symbols:
  <lt_table> type any table,
  <fs_wa>    type any.

lt_mara = value #(
            ( matnr = 1000 mtart = 'FERT' mbrsh = 'M' matkl = 'SD01' )
            ( matnr = 1000 mtart = 'FERT' mbrsh = 'M' matkl = 'SD01' )
            ( matnr = 1001 mtart = 'FERT' mbrsh = 'M' matkl = 'SD01' )
            ( matnr = 1001 mtart = 'FERT' mbrsh = 'M' matkl = 'SD01' )
            ( matnr = 1002 mtart = 'FERT' mbrsh = 'M' matkl = 'SD01' )
            ( matnr = 1003 mtart = 'FERT' mbrsh = 'M' matkl = 'SD01' ) ).

lw_comp_table-name = 'MATNR'.
lw_comp_table-type ?= cl_abap_datadescr=>describe_by_name( 'MATNR' ).
append lw_comp_table to lt_comp_table.

lw_comp_table-name = 'MTART'.
lw_comp_table-type ?= cl_abap_datadescr=>describe_by_name( 'MTART' ).
append lw_comp_table to lt_comp_table.

lw_comp_table-name = 'MBRSH'.
lw_comp_table-type ?= cl_abap_datadescr=>describe_by_name( 'MBRSH' ).
append lw_comp_table to lt_comp_table.

lw_comp_table-name = 'MATKL'.
lw_comp_table-type ?= cl_abap_datadescr=>describe_by_name( 'MATKL' ).
append lw_comp_table to lt_comp_table.

try.
    r_type_struct = cl_abap_structdescr=>create(
      p_components = lt_comp_table ).
  catch cx_sy_table_creation .
endtry.

try.
    r_type_table = cl_abap_tabledescr=>create(
        p_line_type  =  r_type_struct ).
  catch cx_sy_table_creation .
endtry.

create data:
  r_data_tab type handle r_type_table,
  r_data_str type handle r_type_struct.

assign: r_data_tab->* to <lt_table>,
        r_data_str->* to <fs_wa>.

<lt_table> = lt_mara.

loop at <lt_table> assigning <fs_wa> group by ( matnr = '<fs_wa>-matnr' )
                                ascending reference into data(lo_data).
  cl_demo_output=>write( |Group - { lo_data->matnr }| ).
endloop.

loop at lt_mara into lw_mara group by ( matnr = lw_mara-matnr )
                             ascending reference into data(lo_data1).
  cl_demo_output=>write( |Group - { lo_data1->matnr }| ).
endloop.

cl_demo_output=>display( ).

[Sandra_Rossi]

Kenneth Murray Please format your code by using the CODE button (see how clear is the code posted by S Nalluri).

[Sandra_Rossi]

Kenneth Murray I'm surprised that "this is working correctly" because the ABAP documentation says "The data types of key or key1, key2, ... must be known completely and statically". I understand that statically means that the internal table must have a type statically defined for its lines for instance (the type is STRUCTURE here):

TYPES table_type TYPE TABLE OF structure.
FIELD-SYMBOLS <itab> TYPE table_type.
DATA itab TYPE table_type.
LOOP AT itab INTO DATA(struc) GROUP BY ( component = struc-component ... ) ...

S Nalluri Doesn't your code produce a syntax error at LOOP AT GROUP BY because the components are not known statically?

[srikanthnalluri]

sandra.rossi It did not produce any syntax error, it does produce if we try to access the keys with out any - ( ' )

[Sandra_Rossi]

That would be great that people down vote this answer as it's totally wrong and misleading. I have also reported it to the moderator.

S Nalluri Can you post your code as a separate answer so that people can vote for it?

[Sandra_Rossi]

Please format your code by using the CODE button.

[DoanManhQuynh]

I think you can try function method way as I said in this topic.

[DoanManhQuynh]

OK,I did try sometime to write a dynamic group by using function method like bellow, it could be prone error cause I only consider c-like structure:

CLASS zc1_dynamic_grp DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS single_comp IMPORTING comp_name     TYPE char30
                                        struct        TYPE data
                              RETURNING VALUE(return) TYPE string.
    CLASS-METHODS serial_comp IMPORTING comp_names    TYPE string
                                        struct        TYPE data
                              RETURNING VALUE(return) TYPE string.
    CLASS-DATA r_type_struct TYPE REF TO cl_abap_structdescr.
ENDCLASS.
CLASS zc1_dynamic_grp IMPLEMENTATION.
  METHOD single_comp.
    r_type_struct ?= cl_abap_typedescr=>describe_by_data( struct ).
    CHECK r_type_struct->type_kind = cl_abap_typedescr=>typekind_struct1  "flat
       OR r_type_struct->type_kind = cl_abap_typedescr=>typekind_struct2. "deep
    CHECK line_exists( r_type_struct->components[ name = comp_name ] ).
    ASSIGN COMPONENT line_index( r_type_struct->components[ name = comp_name ] ) OF STRUCTURE struct TO FIELD-SYMBOL(<comp_val>).
    return = <comp_val>.
*    r_type_elem ?= cl_abap_typedescr=>describe_by_data( <comp_val> ).
*    CREATE DATA return TYPE HANDLE r_type_elem.
*    ASSIGN return->* TO FIELD-SYMBOL(<return_val>).
*    <return_val> = <comp_val>.
  ENDMETHOD.
  METHOD serial_comp.
    r_type_struct ?= cl_abap_typedescr=>describe_by_data( struct ).
    CHECK r_type_struct->type_kind = cl_abap_typedescr=>typekind_struct1  "flat
       OR r_type_struct->type_kind = cl_abap_typedescr=>typekind_struct2. "deep
    DO.
      TRY.
          DATA(comp_name) = segment( val   = comp_names
                                     index = sy-index
                                     sep   = `,` ).
          CHECK line_exists( r_type_struct->components[ name = comp_name ] ).
          ASSIGN COMPONENT line_index( r_type_struct->components[ name = comp_name ] ) OF STRUCTURE struct TO FIELD-SYMBOL(<comp_val>).
          return = |{ return }{ <comp_val> }|.
        CATCH cx_sy_strg_par_val.
          EXIT.
      ENDTRY.
    ENDDO.
  ENDMETHOD.
ENDCLASS.
TYPES:
  BEGIN OF ty_mara,
    matnr TYPE matnr,
    mtart TYPE mtart,
    mbrsh TYPE mbrsh,
    matkl TYPE matkl,
  END OF ty_mara.
DATA:
  lt_mara TYPE STANDARD TABLE OF ty_mara,
  lw_mara TYPE ty_mara.
DATA:
  r_type_struct TYPE REF TO cl_abap_structdescr,
  r_type_table  TYPE REF TO cl_abap_tabledescr,
  lt_comp_table TYPE cl_abap_structdescr=>component_table,
  lw_comp_table TYPE abap_componentdescr.
DATA:
  r_data_tab TYPE REF TO data,
  r_data_str TYPE REF TO data.
FIELD-SYMBOLS:
  <lt_table> TYPE ANY TABLE,
  <fs_wa>    TYPE any.
START-OF-SELECTION.
  lt_mara = VALUE #(
              ( matnr = 1000 mtart = 'FERT' mbrsh = 'M' matkl = 'SD01' )
              ( matnr = 1000 mtart = 'FERT' mbrsh = 'M' matkl = 'SD01' )
              ( matnr = 1001 mtart = 'AAAA' mbrsh = 'M' matkl = 'SD01' )
              ( matnr = 1001 mtart = 'AAAA' mbrsh = 'A' matkl = 'SD01' )
              ( matnr = 1002 mtart = 'BBBB' mbrsh = 'A' matkl = 'SD01' )
              ( matnr = 1003 mtart = 'BBBB' mbrsh = 'A' matkl = 'SD01' ) ).
  lw_comp_table-name = 'MATNR'.
  lw_comp_table-type ?= cl_abap_datadescr=>describe_by_name( 'MATNR' ).
  APPEND lw_comp_table TO lt_comp_table.
  lw_comp_table-name = 'MTART'.
  lw_comp_table-type ?= cl_abap_datadescr=>describe_by_name( 'MTART' ).
  APPEND lw_comp_table TO lt_comp_table.
  lw_comp_table-name = 'MBRSH'.
  lw_comp_table-type ?= cl_abap_datadescr=>describe_by_name( 'MBRSH' ).
  APPEND lw_comp_table TO lt_comp_table.
  lw_comp_table-name = 'MATKL'.
  lw_comp_table-type ?= cl_abap_datadescr=>describe_by_name( 'MATKL' ).
  APPEND lw_comp_table TO lt_comp_table.
  TRY.
      r_type_struct = cl_abap_structdescr=>create(
        p_components = lt_comp_table ).
    CATCH cx_sy_table_creation .
  ENDTRY.
  TRY.
      r_type_table = cl_abap_tabledescr=>create(
          p_line_type  =  r_type_struct ).
    CATCH cx_sy_table_creation .
  ENDTRY.
  CREATE DATA:
    r_data_tab TYPE HANDLE r_type_table,
    r_data_str TYPE HANDLE r_type_struct.
  ASSIGN: r_data_tab->* TO <lt_table>,
          r_data_str->* TO <fs_wa>.
  <lt_table> = lt_mara.
  LOOP AT <lt_table> ASSIGNING <fs_wa> GROUP BY zc1_dynamic_grp=>single_comp( comp_name = 'MATNR' struct = <fs_wa> )
                                  ASCENDING ASSIGNING FIELD-SYMBOL(<fs_grp1>).
    cl_demo_output=>write( |Group - { <fs_grp1> }| ).
  ENDLOOP.
  LOOP AT <lt_table> ASSIGNING <fs_wa> GROUP BY zc1_dynamic_grp=>serial_comp( comp_names = |MATNR,MBRSH| struct = <fs_wa> )
                                  ASCENDING ASSIGNING FIELD-SYMBOL(<fs_grp2>).
    cl_demo_output=>write( |Group - { <fs_grp2> }| ).
  ENDLOOP.
  cl_demo_output=>display( ).

[Sandra_Rossi]

Bravo! It's a quite fun and interesting coding, I had never seen that before! 🙂

With this coding, the kernel logic of LOOP GROUP BY is revealed: there is a first kernel loop on the internal table <lt_table> where the method is called for each line, its result is grouped into a temporary table, and then a second kernel loop occurs on that temporary table and the code inside LOOP is executed.

I fear about the performance if the internal table is a little big. The algorithm could be optimized by saving the previous result (note that ASSIGN COMPONENT 1 is twice faster than ASSIGN COMPONENT 'MATNR').

Another solution, more performing, is to not use LOOP GROUP BY but doing it a more harsh way: with RTTC to create the temporary HASHED table with only the needed components, <temp_itab> = CORRESPONDING #( itab DISCARDING DUPLICATES ), then loop at <temp_itab>:

TYPES:
  BEGIN OF ty_sflight,
    carrid TYPE sflight-carrid,
    connid TYPE sflight-connid,
  END OF ty_sflight.
DATA:
  lt_sflight   TYPE STANDARD TABLE OF ty_sflight,
  r_type_table TYPE REF TO cl_abap_tabledescr,
  r_data_tab   TYPE REF TO data.
FIELD-SYMBOLS:
  <lt_table>   TYPE ANY TABLE.

START-OF-SELECTION.
  lt_sflight = VALUE #(
               ( carrid = 'AA' connid = 1 )
               ( carrid = 'LH' connid = 1 )
               ( carrid = 'AA' connid = 2 )
               ( carrid = 'LH' connid = 3 ) ).
  r_type_table = cl_abap_tabledescr=>create(
      p_line_type = cl_abap_structdescr=>create(
        p_components = VALUE #(
          ( name = 'CARRID' type = CAST #( cl_abap_datadescr=>describe_by_name( 'SFLIGHT-CARRID' ) ) )
          ( name = 'CONNID' type = CAST #( cl_abap_datadescr=>describe_by_name( 'SFLIGHT-CONNID' ) ) ) ) ) ).
  CREATE DATA r_data_tab TYPE HANDLE r_type_table.
  ASSIGN r_data_tab->* TO <lt_table>.

  <lt_table> = lt_sflight.

  FIELD-SYMBOLS <lt_temp> TYPE HASHED TABLE.
  r_type_table = cl_abap_tabledescr=>create(
      p_line_type = cl_abap_structdescr=>create(
        p_components = VALUE #(
          ( name = 'CARRID' type = CAST #( cl_abap_datadescr=>describe_by_name( 'SFLIGHT-CARRID' ) ) ) ) )
      p_table_kind = cl_abap_tabledescr=>tablekind_hashed
      p_unique = abap_true
      p_key_kind = cl_abap_tabledescr=>keydefkind_tableline ).
  CREATE DATA r_data_tab TYPE HANDLE r_type_table.
  ASSIGN r_data_tab->* TO <lt_temp>.

  <lt_temp> = CORRESPONDING #( <lt_table> DISCARDING DUPLICATES ).

  LOOP AT <lt_temp> ASSIGNING FIELD-SYMBOL(<ls_temp>).
    ASSIGN COMPONENT 1 OF STRUCTURE <ls_temp> TO FIELD-SYMBOL(<field>).
    cl_demo_output=>write( |Group - { <field> }| ).
  ENDLOOP.

  cl_demo_output=>display( ).