###### Technical Articles

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

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) :

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

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.

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

It's okay now, thank you ! 🙂

Thank YOU !!!!

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

BR,

Suhas

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

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

Thank you!

Ivan

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

King regards.