Grouping + aggregation of itab with table comprehensions

Question

Rather typical task but I'm stuck on doing it in a beautiful way.

For example, I need to find the last shipment for each vendor, i.e. to find delivery with the max date for the each vendor

VENDOR     DELIVERY   DATE
10          00055    01/01/2019
20          00070    01/19/2019
20          00088    01/20/2019
20          00120    11/22/2019
40          00150    04/01/2019
40          00200    04/10/2019

The result table to be populated

VENDOR     DELIVERY   DATE
10          00055    01/01/2019
20          00120    11/22/2019
40          00200    04/10/2019

I implemented this in a following way, via DESCENDING, which I find very ugly

LOOP AT itab ASSIGNING <wa> GROUP BY ( ven_no = <wa>-ven_no ) REFERENCE INTO DATA(vendor).
  LOOP AT GROUP vendor ASSIGNING <ven> GROUP BY ( date = <vendor>-date ) DESCENDING.
    CHECK NOT line_exists( it_vend_max[ ven_no = <ven>-ven_no ] ).
    it_vend_max = VALUE #( BASE it_vend_max ( <ven> ) ).
  ENDLOOP.
ENDLOOP.

Is there more elegant way to do this?

I also tried REDUCE

result = REDUCE #( vend_line = value ty_s_vend()
                   MEMBERS = VALUE ty_t_vend( )
                   FOR GROUPS <group_key> OF <wa> IN itab
                   GROUP BY ( key = <wa>-ven_no count = GROUP SIZE
                   ASCENDING
         NEXT vend_line = VALUE #(
              ven_no = <wa>-ven_no

              date  = REDUCE i( INIT max = 0
                                FOR m IN GROUP <group_key>
                                NEXT max = nmax( val1 = m-date
                                                 val2 = <wa>-date ) )
              deliv_no  = <wa>-deliv_no
         MEMBERS = VALUE ty_s_vend( FOR m IN GROUP <group_key> ( m ) ) ).

but REDUCE selects max date from the whole table and it selects only flat structure, which is not what I want. However, in ABAP examples I saw samples where table-to-table reductions are also possible. Am I wrong?

Another thing I tried is finding uniques with WITHOUT MEMBERS but this syntax doesn't work:

it_vend_max = VALUE ty_t_vend( FOR GROUPS value OF <line> IN itab 
                               GROUP BY ( <line>-ven_no <line>-ship_no ) 
                               WITHOUT MEMBERS ( lifnr = value 
                                                 date = nmax( val1 = <line>-date 
                                                              val2 = value-date ) ) ).

Any suggestion of what is wrong here or own elegant solution is appreciated.

I don't understand your remark about "REDUCE selects only **flat structure**", what do you mean? (REDUCE can produce any type you want, from any internal table or any "counting loop" you want) — Sandra Rossi, Apr 12 '19 at 12:15
It just means that I’m incompetent with REDUCE :( How to reduce table to table with MAX aggregation? What’s the syntax? My REDUCE produces only structure, how to make it produce grouped table? If I put table type instead of # it throws error in that form of REDUCE — Suncatcher, Apr 12 '19 at 14:22

Sandra Rossi · Accepted Answer · 2019-04-12T16:03:22.267

If not too complex, I think it's best to use one construction expression, which shows that the goal of the expression is to initialize one variable and nothing else.

The best I could do to be the most performing and the shortest possible, but I can't make it elegant:

TYPES ty_ref_s_vend TYPE REF TO ty_s_vend.

result = VALUE ty_t_vend(
    FOR GROUPS <group_key> OF <wa> IN itab
    GROUP BY ( ven_no = <wa>-ven_no ) ASCENDING
    LET max2 = REDUCE #(
        INIT max TYPE ty_ref_s_vend
        FOR <m> IN GROUP <group_key>
        NEXT max = COND #( WHEN max IS NOT BOUND
                             OR <m>-date > max->*-date
                           THEN REF #( <m> ) ELSE max ) )
    IN ( max2->* ) ).

As you can see I use a data reference (aux_ref_s_vend2) for a better performance, to point to the line which has the most recent date. It's theoretically faster than copying the bytes of the whole line, but it's less readable. If you don't have a huge table, there won't be a big difference between using an auxiliary data reference or an auxiliary data object.

PS: I could not test it because the question does not provide a MCVE.

Here is another solution if you really want to use REDUCE in the primary constructor expression (but it's not needed):

result = REDUCE ty_t_vend(
    INIT vend_lines TYPE ty_t_vend
    FOR GROUPS <group_key> OF <wa> IN itab
    GROUP BY ( ven_no = <wa>-ven_no ) ASCENDING
    NEXT vend_lines = VALUE #(
        LET max2 = REDUCE ty_ref_s_vend(
            INIT max TYPE ty_ref_s_vend
            FOR <m> IN GROUP <group_key>
            NEXT max = COND #( WHEN max IS NOT BOUND
                                 OR <m>-date > max->*-date
                               THEN REF #( <m> ) ELSE max ) )
        IN BASE vend_lines
        ( max2->* ) ) ).

Wow! Nested `ty_s_likp` structure! Why so complicated? :) I’d rather [keep it simple](https://en.wikipedia.org/wiki/KISS_principle) — Suncatcher, Apr 12 '19 at 10:42
I was confused by all your different attempts and self-questioning, and LIKP being a table, the delivery number had to be a component of it... Could you please edit the question to make it resemble your case? (BTW currently it compiles on any ABAP system, not only ECC) — Sandra Rossi, Apr 12 '19 at 11:28
Let’s just we put aside all the SAP tables, and consider this simple case: vendor, delivery number and date. My case is derived from SRM and I don’t want to complicate everything with specialities — Suncatcher, Apr 12 '19 at 11:45
Indeed, it works brilliantly, both variants, and it damn beautiful! Though not readable, haha — Suncatcher, Apr 15 '19 at 11:24

score 1 · Answer 2 · answered Apr 14 '19 at 21:13

1

what do you mean by elegant solution? Using GROUP or REDUCE with the "new" abap syntax is not making it elegant in any way, at least for me...

For me, coding that is easily understandable for everyone is elegant:

SORT itab BY vendor date DESCENDING.
DELETE ADJACENT DUPLICATES from itab COMPARING vendor.

Or if the example is more complex, a simple LOOP AT with IF or AT in it APPENDING aggregated lines to a new itab, will also solve it. Example here.

answered Apr 14 '19 at 21:13

futu

868
6
12

`DELETE ADJACENT` construction is short, yes, but is not applicable if the input table type is sorted, so its area of application is too narrow. Requirement of additional table conversion makes it not so beautiful. – Suncatcher Apr 15 '19 at 08:40
2

i totally agree with as well from non technical point of view. using complex syntax is not elegant all. it is not maintainable at ALL. – Haojie Apr 16 '19 at 01:05

Grouping + aggregation of itab with table comprehensions

2 Answers2

Linked