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!
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.
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.
Table of Content – ABAP 740 Concepts
- ABAP 740 – NEW Operator to instantiate the objects
- ABAP 740 – NEW Operator to create ITAB entries
- ABAP 740 – VALUE Operator to create ITAB entries
- ABAP 740 – Table Expressions to Read & Modify ITAB line
- ABAP 740 – LINE_EXISTS to check record in ITAB
- ABAP 740 – Meshes – A new complex type of Structures
- ABAP 740 – Mesh Path – Forward and Inverse Association
- ABAP 740 – FOR Iteration Expression
- ABAP 740 SWITCH – Conditional Operator
- ABAP 740 – LOOP AT with GROUP BY
- ABAP 740 – Is CONSTANT not a Static Attribute anymore?
- SALV IDA (Integrated Data Access) – Introduction
- SALV IDA – Selection Conditions
- SALV IDA – New Calculated Fields
- SALV IDA – Column Settings
- SALV IDA – Add and Handle Hotspot (Hyperlink)