Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

How do I find the most repeated value in an internal table's column?

0 Kudos

Hi,

I am having issues with finding a way to find the most repeated value in an itab's column. My specific case is finding the most repeated number in a column of student's grades, I have tried going through the column with a for loop but I cannot seem to find a way to do it. Can you please help me?

1 ACCEPTED SOLUTION

Sandra_Rossi
Active Contributor

Could be something like that. Please ask if you need any clarification:

TYPES : BEGIN OF z311_anotat,
          student TYPE string,
          nota    TYPE c LENGTH 1,
        END OF z311_anotat.
DATA: wa_notat TYPE z311_anotat,
      it_notat LIKE TABLE OF wa_notat.
it_notat = VALUE #(
    ( student = 'John' nota = 'A' )
    ( student = 'Lisa' nota = 'C' )
    ( student = 'Abdu' nota = 'B' )
    ( student = 'Wang' nota = 'C' )
    ( student = 'Gaby' nota = 'A' )
    ( student = 'Marc' nota = 'C' ) ).
    
DATA(most_rep_nota) = REDUCE #(
    INIT most_rep_nota2 = ``
         max_count      = 0
    FOR GROUPS <g_notat> OF <notat> IN it_notat
    GROUP BY ( nota = <notat>-nota count = GROUP SIZE )
    NEXT most_rep_nota2 = COND #( WHEN <g_notat>-count > max_count
                                  THEN |{ <g_notat>-nota } occurs { <g_notat>-count } times|
                                  ELSE most_rep_nota2 )
         max_count      = COND #( WHEN <g_notat>-count > max_count THEN <g_notat>-count ELSE max_count ) ).

ASSERT most_rep_nota = |C occurs 3 times|.
6 REPLIES 6

Sandra_Rossi
Active Contributor

Your initial table:

grade
-----
A
C
B
C
A
C

A new table that you want to get:

grade   count
-----   -----
A       2
B       1
C       3

What did you try?

eduardo_heredia
Explorer
0 Kudos

Hi Edis, you can create a new table as Sandra suggested with grade and count columns, after that you sort that table like this_

SORT it_newtab COUNT DESCENDING

so you will have the most repeated value at the top

you just need to read that table with index 1

0 Kudos

This is the way I tried to filter the most repeated element in the table, "notat" means grades

DATA: lv_prev TYPE i,
lv_final TYPE i,
lv_repcount TYPE i,
lv_maxi TYPE i VALUE 1,
lv_curr TYPE i.

DATA: wa_notat TYPE z311_anotat,

it_notat LIKE TABLE OF wa_notat.

SORT it_notat BY nota.
LOOP AT it_notat INTO wa_notat.
IF lv_prev IS INITIAL.
lv_prev = wa_notat-nota.
ENDIF.
IF lv_prev NE wa_notat-nota.

IF lv_repcount > lv_maxi.
lv_maxi = lv_repcount.
lv_final = lv_prev.
lv_prev = wa_notat-nota.

ENDIF.
lv_repcount = 1.
lv_prev = wa_notat-nota.

CONTINUE.
ENDIF.
lv_repcount = lv_repcount + 1.
lv_curr = wa_notat-nota.
AT LAST.
IF lv_repcount > lv_maxi.
lv_maxi = lv_repcount.
lv_final = lv_curr.
ENDIF.
ENDAT.

ENDLOOP.

Sandra_Rossi
Active Contributor

Could be something like that. Please ask if you need any clarification:

TYPES : BEGIN OF z311_anotat,
          student TYPE string,
          nota    TYPE c LENGTH 1,
        END OF z311_anotat.
DATA: wa_notat TYPE z311_anotat,
      it_notat LIKE TABLE OF wa_notat.
it_notat = VALUE #(
    ( student = 'John' nota = 'A' )
    ( student = 'Lisa' nota = 'C' )
    ( student = 'Abdu' nota = 'B' )
    ( student = 'Wang' nota = 'C' )
    ( student = 'Gaby' nota = 'A' )
    ( student = 'Marc' nota = 'C' ) ).
    
DATA(most_rep_nota) = REDUCE #(
    INIT most_rep_nota2 = ``
         max_count      = 0
    FOR GROUPS <g_notat> OF <notat> IN it_notat
    GROUP BY ( nota = <notat>-nota count = GROUP SIZE )
    NEXT most_rep_nota2 = COND #( WHEN <g_notat>-count > max_count
                                  THEN |{ <g_notat>-nota } occurs { <g_notat>-count } times|
                                  ELSE most_rep_nota2 )
         max_count      = COND #( WHEN <g_notat>-count > max_count THEN <g_notat>-count ELSE max_count ) ).

ASSERT most_rep_nota = |C occurs 3 times|.

touzik_itc
Active Participant
0 Kudos

If data are stored in a database table zabcd:

SELECT grade, COUNT( * ) AS count  FROM zabcd UP TO 1 ROWS INTO @DATA(ls_max) 
  GROUP BY grade ORDER BY count DESCENDING.
ENDSELECT.