0

I would like to sum the values inside my internal table by name, article, date (year) and price, but the problem is that my collect statement is not working. I think this has something to do with my date value which is of YYYY-MM-DD, therefore the collect statement makes a difference between 2014-10-12 and 2014-11-12 and inserts them as two different values.

How can I change the collect statement, so that it knows "2014-10-12" is the same as "2014-11-12".

My table

Hans - Mouse - 80 - 2014-12-01
Hans - Mouse - 80 - 2014-05-01
Albert - Keyboard - 50 - 2015-05-04
Albert - Keyboard - 80 - 2015-10-06
Albert - Keybaoard - 100 - 2016-01-01

What I want

Hans - Mouse - 160 - 2014
Albert - Keyboard - 130 - 2015
Albert - Keybaoard - 100 - 2016

My Code

SELECT * FROM gv_table INTO CORRESPONDING FIELDS OF wa_table
  WHERE date(4) BETWEEN '20140101' AND '20160101' <-- date(4) is not working. It gives me an error
 

  COLLECT wa_table INTO lt_table.
ENDSELECT.

Or do I have to loop a second time thru my lt_table and do another collect?

EDIT

I have a global table:

Hans - Mouse - 60 - 2014-12-02
Hans - Mouse - 50 - 2014-12-02
Peter - Keyboard - 40 - 2014-03-02

What I want my local table to look like:

Hans - Mouse - 60 - 2014
Hans - Mouse - 50 - 2014
Peter - Keyboard - 40 - 2014

And then aggregate it:

Hans - Mouse 110 - 2014
Peter - Keyboard - 40 - 2014
Suncatcher
  • 10,355
  • 10
  • 52
  • 90
Andrew
  • 4,264
  • 1
  • 21
  • 65
  • 1
    Please clarify your question, you indicate the dates 2014-10-12 and 2014-11-12, but they are not in your example. You should indicate what you currently obtain instead of saying "not work". Indicating the expected result is not sufficient when asking a question at Stack Overflow. Moreover, you should provide a **reproducible** example, so you should provide the input data as part of your code. Thank you. – Sandra Rossi Jan 14 '21 at 15:08

1 Answers1

4

The COLLECT statement is unfortunately not very flexible. It will consider the primary key of your internal table as keys to aggregate on. If you didn't declare an explicit primary key when you declared your table, then that means all non-numeric fields. And there is no way to tell it to perform any transformations on the keys or values before aggregating.

If you insist on using the COLLECT statement, then a possible workaround is:

  1. Create a separate type for your sum structure with only the fields you want. When you want your date represented by only the year, create a separate field of type GJAHR (or your preferred TYPE n LENGTH 4)
  2. Create a table and a work-area of that type
  3. In your loop, write the date to your new 4-digit date of the work area (wa_sum-year = wa_table-date(4).) You can likely fill the rest with MOVE-CORRESPONDING.
  4. COLLECT your output work-area into your output table.

But a more modern solution could be to create a sum table using the constuctor expressions which were added in SAP_BASIS 7.40.

And from SAP_BASIS 7.50(?) on you can also use the LEFT aggretate function in your SQL statement to already truncate the date on the database. And if you do that, you can even let the database do the aggregation by using GROUP BY:

SELECT name, product, SUM( quantity ) AS sum, LEFT( date, 4) AS year 
  FROM dbtab 
  INTO CORRESPONDING FIELDS OF @wa_table
  WHERE date BETWEEN '20140101' AND '20160101' 
  GROUP BY name, product, LEFT( date, 4 ).
Philipp
  • 67,764
  • 9
  • 118
  • 153
  • Is it also possible to do this by just temporary saving the date value in a variable and then comparing it? I don't know If I am allowed to use a second table here and I for sure know that I can't use the "sum table" way.. The only problem I have is that I don't know how to change my data type while putting it inside my internal table. I've edited my question. – Andrew Jan 14 '21 at 14:17
  • Why wouldn't you be "allowed" to do this? – Philipp Jan 14 '21 at 14:20
  • I don't think that my professor would like that answer.. I've edited my post, maybe you have another solution. My professor says "to make your code work, extract the date in another variable". I don't know what he wants to say with that to me. – Andrew Jan 14 '21 at 14:23
  • I can't read the mind of your professor. But this is basically what happens in point 3 of my numbered list. – Philipp Jan 14 '21 at 14:24
  • So if I understand your answer correctly, I would need two tables for my solution right? In the first lt_table I add the data from my gv_table with the date YYYY-MM-DD. And then I loop thru it, add the data to the second table, change the date to YYYY and say "Collect"? – Andrew Jan 14 '21 at 14:31
  • You don't need any more tables than in your original solution. The source of your data appears to be a (weirdly named) database table you extract via a SELECT loop. You can keep doing that (but you will have to declare two different structure types for the work areas). – Philipp Jan 14 '21 at 14:37
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/227337/discussion-between-andrew-and-philipp). – Andrew Jan 14 '21 at 14:38