how to get the duplicate entries from an internal table

[Former Member]

Hi ,

I want to list the duplicate entries from an internal table. If the internal table A has all the entries ,

how can I display the duplicates alone. Rather I want to delete only the unique records from the internal table.

Table A

ID Field1 Field2

1 1122 Green

2 2345 Green

3 2345 Blue

4 1122 Green

5 5555 Red

6 5555 Red

The output I want is

ID Field1 Field2

1 1122 Green

4 1122 Green

5 5555 Red

6 5555 Red

Thanks

Gayathri

[andreas_mann3]

Hi,

Data B like A occurs 0 with header line.

Data tab_duplicate like A occurs 0 with header line.

Data tab_unicate like A occurs 0 with header line.

B[] = A[].

Loop at A.

Read table B with key field1 = A-field1

field2 = A-field2.

If sy-subrc = 0.

Append A to tab_duplicate.

Else.

Append A to tab_unicate.

Endif.

Endloop.

*output duplicates

Loop at tab_duplicate.

Write: / ...

Endloop.

Regards Andreas

Message was edited by: Andreas Mann

[juan_suros]

Try this:

SORT TableA.
DELETE ADJACENT DUPLICATES FROM TableA.

It should work, unless the "id" you mention is a field with contents.

[RichHeilman]

Here is a solution.

report zrich_0003 .

data: begin of itab occurs 0,
      field1(4) type c,
      field2(10) type c,
      end of itab.

data: counter type i.

start-of-selection.

  itab = '1122Green'. append itab.
  itab = '2345Green'. append itab.
  itab = '2345Blue'. append itab.
  itab = '1122Green'. append itab.
  itab = '5555Red'. append itab.
  itab = '5555Red'. append itab.

  loop at itab.

    clear counter.
    loop at itab where field1 = itab-field1
                   and field2 = itab-field2.
      counter = counter + 1.
    endloop.
    if counter = 1.
      delete itab.
    endif.

  endloop.

  loop at itab.
    write:/ itab-field1, itab-field2.
  endloop.

Regards,

Rich Heilman

[Former Member]

Hi ,

This takes a long time (more than one day )if the table contains millions of records. But the logic works fine. Is there any other logic to do this with improved performance .

Thanks

Gayathri

[Former Member]

Hi Gayathri,

I can give you pseudo-code, I am sure after which you should not have a problem in getting your code written.

Sort the itab by appropriate keys.
Loop at itab.

Read previous record.
if previous record = current record.
   continue.
else.
   read next record.
   if current record = next record.
      continue.
   else.
      delete current record.
   endif.
endif.
endloop.

Unlike Rich's code, this contains only one loop.

To read next record, you need to get hold of sy-tabix and place it in a variable for eg. lv_tabix , increment by 1 and do a read statement.

This should improve performance considerably.

Let us know how it goes.

Regards,

Subramanian V.

[Former Member]

Hi,

Go to SE30 and press F6. This will take you to the tips and tricks section. There, you will find some algorithms on how to effeciently deal with internal tables for loops and searching. See if these help.

The parallel cursor approach is quite handy in keeping processing times linear.

Regards

Message was edited by: Shehryar Khan

[Former Member]

Hi ,

As per my Knowledge Using two internal table is best option.I Think it does give any performance Issue.I using same logic in all my Reports. In add+ that Use

TCODE:ST05 "Performance Ananlysis Active and in Display of that Trace File Menu

select Trace List---> display identical selects

[ChristianFi]

The 2 tables approach (as described above) has a performance issue regarding the read with key statement which performs a linear search.

Even with a faster collect and a delete you must consider the additional memory consuption of the 2nd table (Since we are talking of millions of entries you may not neglect that fact)

Sorting and comparing the records in one single loop is in my opion the most stable way (and it keeps memory low)

I do not see how ST05 could help in that issue (beside the fact the maybe the duplicates could be detected when (if ever) the data is retrieved from database).

Christian

[jayanthi_jayaraman]

Hi,

try this.

data : itab type standard table of zzz_tablea with header line,

itab1 type standard table of zzz_tablea with header line.

select * from zzz_tablea into table itab1.

SELECT field1 field2

INTO corresponding fields of table itab

FROM zzz_tablea

GROUP BY field1 field2

HAVING COUNT( * ) > 1.

loop at itab.

loop at itab1 where field1 = itab-field1

and field2 = itab-field2.

WRITE: / itab1-id, itab-field1, itab-field2.

endloop.

endloop.