topic Re: Retrieve value in Application Development and Automation Discussions

Retrieve value

former_member748613 — Tue, 20 Jul 2021 11:43:36 GMT

Im having a little trouble building the logic.

I need to retrieve the highest concat column value per material

Re: Retrieve value

LaurensDeprost — Tue, 20 Jul 2021 12:49:34 GMT

Hi Adrian,

How exactly are you having trouble/what have you tried to achieve the desired result?
Have you tried searching the Q&A section for similar questions?

Re: Retrieve value

former_member748613 — Tue, 20 Jul 2021 12:56:48 GMT

Yes, I already searched for some Q&A sections, I only saw on how to get the highest value on the column. But what I need is to get the highest value in concat column per material

Re: Retrieve value

Dominik_Tylczynski — Tue, 20 Jul 2021 13:33:26 GMT

Hello kuser0320

Just sort the table by CONCAT column descending and read the first row - see SAP Help SORT itab

Best regard

Dominik Tylczynski

Re: Retrieve value

VXLozano — Tue, 20 Jul 2021 13:36:55 GMT

If you just need to order the data:

when selecting the data: ORDER BY
after selection: SORT BY

If you need a new table with just the highest values:

when selecting the data: MAX( )
after selection: LOOP + INSERT (not sure if there are new syntax for these things, I'm sure they will be)

But if you have problem with this "logic", you need to go back to the basics of programming, I fear. No offense.

Re: Retrieve value

LaurensDeprost — Tue, 20 Jul 2021 13:37:14 GMT

Hi Adrian,

Take a look at the documentation for the openSQL GROUP BY clause.

Adjust your original database query if possible to avoid selecting unnecessary data.
Alternatively, if your system supports it (>7.40), you can select from your internal table:

 SELECT
   FROM @original_itab AS material
   FIELDS material~matnr,
          MAX( material~concat ) AS max_concat_for_material
   GROUP BY material~matnr
   INTO TABLE @DATA(materials_with_max_concat).

Finally, another option is sorting your itab, loop over it, and extract the maximum value per material number.

Re: Retrieve value

former_member748613 — Tue, 20 Jul 2021 13:38:37 GMT

Hello Dominik, I can't just sort concat column. Your solution will just give me the highest value in concat column. But what I need is the highest value for every matnr column

Re: Retrieve value

former_member598787 — Tue, 20 Jul 2021 13:39:25 GMT

Hello ,

Please refer to below piece of code:

Output is :

2562 20251

2562 20221

2705 20211

2705 20201

types: begin of ty_mara,
        matnr type matnr,
        concat(15) type c,
      end of ty_mara.
types: ty_t_mara type standard table of ty_mara.
data: ls_mara type ty_mara.
data : lt_mara type standard table of ty_mara.
ls_mara-matnr = '000000000000002705'.
ls_mara-concat = '20201'.
append ls_mara to lt_mara.
ls_mara-matnr = '000000000000002705'.
ls_mara-concat = '20211'.
append ls_mara to lt_mara.
ls_mara-matnr = '000000000000002562'.
ls_mara-concat = '20251'.
append ls_mara to lt_mara.
ls_mara-matnr = '000000000000002562'.
ls_mara-concat = '20221'.
append ls_mara to lt_mara.
sort lt_mara by matnr  concat descending.
clear ls_mara.
loop at lt_mara assigning field-symbol(<lfs_mara>).
    write :/ <lfs_mara>-matnr .
    write : <lfs_mara>-concat.
endloop.

Re: Retrieve value

Dominik_Tylczynski — Tue, 20 Jul 2021 13:43:41 GMT

kuser0320

You are right. I somehow missed that part. Still the other answer give good solutions.