Gyan Factory

The great new addition to old and gold LOOP AT is a GROUP BY in ABAP 740. This is an amazing addition to LOOP AT. Lets check it out!

Introduction

LOOP AT must be one of most commonly used syntax. You must be using that often as well. In ABAP 740, this new great addition GROUP BY has amazing capabilities.

Syntax

This is highlevel syntax using the GROUP BY

 
LOOP AT t_customres INTO DATA(ls_cust1)
     GROUP BY  ( route = ls_cust1-route
                 SIZE  = GROUP SIZE
                 INDEX = GROUP INDEX )
      ASCENDING
      REFERENCE INTO DATA(route_group).

LOOP AT with GROUP BY

As the name suggests, GROUP BY addition groups the rows of the ITAB and the executes the LOOP across the groups. The loop gets executed in two phases:

Group – A loop would be executed on the Group which would be created by GROUP condition
Group Loop – A loop would be on each group to access the rows of the group. This are members.

Sounds amazing, huh?

Things to Remember

Few points to remember whenever you need to use this GROUP BY:

Group Key – You can specify any fields from the ITAB as part of the key.
Sorting order – default sorting order of the field is ASCENDING. But you can also specify ASCENDING or DESCENDING explicitly
Group members – If don’t need to access the members, you can use the WITHOUT MEMBERS

Lets dip dive in the Examples and learn more.

Example 1 – Get Unique values using the GROUP BY

I’m using the same customer/route test data which I have used in other ABAP 740 examples. In this example 1, we’ll list down the unique Routes by creating the group based on the field ROUTE. The output would be also captured in the result variable created automatically.

Also notice the use of the WITHOUT MEMBERS as members were not accessed at all.

TYPES:
  BEGIN OF ty_customer,
    customer TYPE char10,
    NAME     TYPE char30,
    city     TYPE char30,
    route    TYPE char10,
  END   OF ty_customer.
TYPES: tt_customers TYPE SORTED TABLE OF ty_customer
          WITH UNIQUE KEY customer.
 
TYPES: tt_citys TYPE STANDARD TABLE OF char30 WITH EMPTY KEY.
 
DATA(t_customres) =
VALUE tt_customers(
  ( customer = 'C0001' NAME = 'Test Customer 1' city = 'NY' route = 'R0001' )
  ( customer = 'C0002' NAME = 'Customer 2'      city = 'LA' route = 'R0003' )
  ( customer = 'C0003' NAME = 'Good Customer 3' city = 'DFW' route = 'R0001' )
  ( customer = 'C0004' NAME = 'Best Customer 4' city = 'CH' route = 'R0003' )
  ( customer = 'C0005' NAME = 'So So Customer 5' city = 'NY' route = 'R0001' )
).
 
 
* Simply get the unique Routes, use WITHOUT MEMBERS
LOOP AT t_customres INTO DATA(ls_cust_2)
     GROUP BY  ( route = ls_cust_2-route )
      ASCENDING
      WITHOUT MEMBERS
      REFERENCE INTO DATA(route_group_2).
 
  WRITE: / route_group_2->route.
 
 
ENDLOOP.

The output would be like:

Example 2 – Usage of MEMBERS, SIZE and INDEX

The 2nd phase of the GROUP BY is the access the members of the LOOP. To get the members of the group, we need to LOOP AT GROUP within the main LOOP. This inner LOOP needs to use the group result to get the members. The new BASE addition of VALUE will provide all the field values for non group fields. More on BASE in upcoming articles.

There are two inbuilt additions for the GROUP BY key.
SIZE – This would have the number of rows in that group
INDEX – Would be index number of the group, starting with 1.

When the single field is used to make the key, the result would be created as the single field as shown in earlier example. When there are more than one field, the automatic variable would contain all those fields, as in this example.

* Creates a group of Route
*   with members as all fields which matches the group
LOOP AT t_customres INTO DATA(ls_cust1)
     GROUP BY  ( route = ls_cust1-route
                 SIZE  = GROUP SIZE
                 INDEX = GROUP INDEX )
      ASCENDING
      REFERENCE INTO DATA(route_group).
 
  WRITE: / 'Group', route_group->index.
  WRITE: / route_group->route.
 
  DATA(members) = VALUE tt_customers( ).
  LOOP AT GROUP route_group ASSIGNING FIELD-SYMBOL(<route>).
    members = VALUE #( BASE members ( <route> ) ).
  ENDLOOP.
 
  LOOP AT members INTO DATA(ls_member).
    WRITE: /(5)", ls_member-customer, ls_member-name, ls_member-city.
  ENDLOOP.
  WRITE: /(5)", 'Group size ', route_group->size.
  SKIP 2.
ENDLOOP.

The output of this example is like this:

In Debugging, you can see the created structure which is automatically created.

Example 3 – Group By without explicit Result variable

You can also choose not to specify the result variable at all. If you do so, the workarea used for the LOOP AT would be used for the putting the group data as well. But it would be the entire first matching record with group key.

 
* Creates a group of Route and City
*  but without using the Group result
LOOP AT t_customres INTO DATA(ls_cust)
     GROUP BY  ( route = ls_cust-route
                 city  = ls_cust-city )
      ASCENDING.
 
  WRITE: / ls_cust.
 
  DATA(members) = VALUE tt_customers( ).
  LOOP AT GROUP ls_cust ASSIGNING FIELD-SYMBOL(<route>).
    members = VALUE #( BASE members ( <route> ) ).
  ENDLOOP.
 
  LOOP AT members INTO DATA(ls_member).
    WRITE: /(5)", ls_member-customer, ls_member-name.
  ENDLOOP.
  SKIP 2.
ENDLOOP.

Output this example 3. As you can see here the entire first row of the group is being set in the LOOP.

Takeaway from GROUP BY

This would be great addition to the GROUP BY. Also, I think it would make sense to use the explicit result work area all the time, instead of using the main loop workarea. It would be more confusing to use the main workarea.

Gyan Factory - SAP Technical Project support

Tuesday, February 16, 2016

ABAP 740 – LOOP AT with GROUP BY

Introduction

Syntax

LOOP AT with GROUP BY

Things to Remember

Example 1 – Get Unique values using the GROUP BY

Example 2 – Usage of MEMBERS, SIZE and INDEX

Example 3 – Group By without explicit Result variable

Takeaway from GROUP BY

Table of Content – ABAP 740 Concepts

No comments: