topic Re: Delete no duplicate records from internal table in Application Development and Automation Discussions

Delete no duplicate records from internal table

Former Member — Thu, 20 Sep 2007 09:35:23 GMT

how can i delete no duplicate record from an internal table is there any command for this.

I want to delete only those record which is not duplicated in internal table means if there are 10 records in internal table 3 records are having duplicate entries and 2 records having single entries then only those 2 records should get deleted. exactly opposite to 'delete adjacent duplicate'

Message was edited by:

amit manglik

Re: Delete no duplicate records from internal table

Former Member — Thu, 20 Sep 2007 09:36:46 GMT

first SORT the ITAB with key fields

the use the command

DELETE ADJACENT DUPLICTAES FROM ITAB comparing Field1 field2..

Regards

Anji

Re: Delete no duplicate records from internal table

former_member404244 — Thu, 20 Sep 2007 09:38:40 GMT

Hi,

Deleting Lines

To delete a single line of any internal table, use the DELETE statement. You can either use the table key to find and delete a single line using its key, delete a set of lines that meet a condition, or find and delete neighboring duplicate entries. If the table has a non-unique key and there are duplicate entries, the first entry is deleted.

Deleting a Line Using the Table Key

To use the table key of table <itab> as a search key, use one of the following statements:

DELETE TABLE <itab> FROM <wa>.

DELETE TABLE <itab> WITH TABLE KEY <k1> = <f 1> ... <k n> = <f n>.

In the first case, <wa> must be a work area compatible with the line type of <itab>. The values of the key fields are taken from the corresponding components of the work area.

In the second case, you have to supply the values of each key field explicitly. If you do not know the name of one of the key fields until runtime, you can specify it as the content of a field <n i > using the form (<n i >) = <f i >. If the data types of <f i > are not compatible with the key fields, the system converts them.

The system searches for the relevant lines as follows:

Standard tables

Linear search, where the runtime is in linear relation to the number of table entries.

Sorted tables

Binary search, where the runtime is in logarithmic relation to the number of table entries.

Hashed tables

The entry is found using the hash algorithm of the internal table. The runtime is independent of the number of table entries.

If the system finds a line, it deletes it from the table and sets SY-SUBRC to zero. Otherwise, SY-SUBRC is set to 4. If the table has a non-unique key and the system finds duplicate entries, it deletes the first entry.

Deleting Several Lines Using a Condition

To delete more than one line using a condition, use the following statement:

DELETE <itab> WHERE <cond>.

This processes all of the lines that meet the logical condition <cond>. The logical condition can consist of more than one comparison. In each comparison, the first operand must be a component of the line structure. If the table lines are not structured, the first operand can also be the expression TABLE LINE. The comparison then applies to the entire line. If at least one line is deleted, the system sets SY-SUBRC to 0, otherwise to 4.

Deleting Adjacent Duplicate Entries

To delete adjacent duplicate entries use the following statement:

DELETE ADJACENT DUPLICATE FROM <itab>

[COMPARING <f1> <f 2> ...

|ALL FIELDS].

The system deletes all adjacent duplicate entries from the internal table <itab>. Entries are duplicate if they fulfill one of the following compare criteria:

Without the COMPARING addition, the contents of the key fields of the table must be identical in both lines.

If you use the addition COMPARING <f1> <f 2> ... the contents of the specified fields <f 1 > <f 2 > ... must be identical in both lines. You can also specify a field <f i > dynamically as the contents of a field <n i > in the form (<n i >). If <n i > is empty when the statement is executed, it is ignored. You can restrict the search to partial fields by specifying offset and length.

If you use the addition COMPARING ALL FIELDS the contents of all fields of both lines must be identical.

You can use this statement to delete all duplicate entries from an internal table if the table is sorted by the specified compare criterion.

If at least one line is deleted, the system sets SY-SUBRC to 0, otherwise to 4.

Examples

DATA: BEGIN OF LINE,

COL1 TYPE I,

COL2 TYPE I,

END OF LINE.

DATA ITAB LIKE HASHED TABLE OF LINE WITH UNIQUE KEY COL1.

DO 4 TIMES.

LINE-COL1 = SY-INDEX.

LINE-COL2 = SY-INDEX ** 2.

INSERT LINE INTO TABLE ITAB.

ENDDO.

LINE-COL1 = 1.

DELETE TABLE ITAB: FROM LINE,

WITH TABLE KEY COL1 = 3.

LOOP AT ITAB INTO LINE.

WRITE: / LINE-COL1, LINE-COL2.

ENDLOOP.

The output is:

2 4

4 16

The program fills a hashed table with a list of square numbers. The DELETE statement delete the lines from the table where the key field COL1 has the contents 1 or 3.

DATA: BEGIN OF LINE,

COL1 TYPE I,

COL2 TYPE I,

END OF LINE.

DATA ITAB LIKE HASHED TABLE OF LINE WITH UNIQUE KEY COL1.

DO 4 TIMES.

LINE-COL1 = SY-INDEX.

LINE-COL2 = SY-INDEX ** 2.

INSERT LINE INTO TABLE ITAB.

ENDDO.

DELETE ITAB WHERE ( COL2 > 1 ) AND ( COL1 < 4 ).

LOOP AT ITAB INTO LINE.

WRITE: / LINE-COL1, LINE-COL2.

ENDLOOP.

The output is:

1 1

4 16

The program fills a hashed table with a list of square numbers. The DELETE statement deletes the lines of the table where the content of field COL2 is greater than 1 and the content of field COL1 is less than 4.

DATA OFF TYPE I.

DATA: BEGIN OF LINE,

COL1 TYPE I,

COL2 TYPE C,

END OF LINE.

DATA ITAB LIKE STANDARD TABLE OF LINE

WITH NON-UNIQUE KEY COL2.

LINE-COL1 = 1. LINE-COL2 = 'A'. APPEND LINE TO ITAB.

LINE-COL1 = 1. LINE-COL2 = 'B'. APPEND LINE TO ITAB.

LINE-COL1 = 2. LINE-COL2 = 'B'. APPEND LINE TO ITAB.

LINE-COL1 = 3. LINE-COL2 = 'B'. APPEND LINE TO ITAB.

LINE-COL1 = 4. LINE-COL2 = 'B'. APPEND LINE TO ITAB.

LINE-COL1 = 5. LINE-COL2 = 'A'. APPEND LINE TO ITAB.

OFF = 0. PERFORM LIST.

DELETE ADJACENT DUPLICATES FROM ITAB COMPARING ALL FIELDS.

OFF = 14. PERFORM LIST.

DELETE ADJACENT DUPLICATES FROM ITAB COMPARING COL1.

OFF = 28. PERFORM LIST.

DELETE ADJACENT DUPLICATES FROM ITAB.

OFF = 42. PERFORM LIST.

FORM LIST.

SKIP TO LINE 3.

LOOP AT ITAB INTO LINE.

WRITE: AT /OFF LINE-COL1, LINE-COL2.

ENDLOOP.

ENDFORM.

The output is:

1 A 1 A 1 A 1 A

1 A 1 B 2 B 2 B

1 B 2 B 3 B 5 A

2 B 3 B 4 B

3 B 4 B 5 A

4 B 5 A

5 A

The example creates and fills a standard table. Here, the first DELETE statement deletes the second line from ITAB because the second line has the same contents as the first line. The second DELETE statement deletes the second line from the remaining table because the contents of the field COL1 is the same as in the first line. The third DELETE statement deletes the third and fourth line from the remaining table because the contents of the default key field COL2 are the same as on the second line. Although the contents of the default key are the same for the first and the fifth line, the fifth line is not deleted because it is not adjacent to the first line.

REWARD points if u find useful..

Regards,

Nagaraj

Re: Delete no duplicate records from internal table

Former Member — Thu, 20 Sep 2007 09:41:22 GMT

hi,

try this.

SORT itab BY <ur field>.

DELETE ADJACENT DUPLICATES FROM itab.

Re: Delete no duplicate records from internal table

VXLozano — Thu, 20 Sep 2007 09:43:03 GMT

If you want to delete the

NON

duplicated ones, you will need to find an algorythm by yourself, I doubt SAP would implement a FM for do that (but I can be wrong, as usual).

Try to copy your internal table into another one, and work by looping the second one... something like:

loop at itab1 into wa1.
  loop at itab2 into wa2 where field_list = field_list.
  endloop.
  if sy-tabix > 1.
    delete itab1 where field_list = wa1.
  endif.
endloop.

It's not a real program, just some sort of pseudo-coding... it will be up to you to make it work

I wish someone with better knowledge about it can lend you a hand, if not, try to use my answer

Oh, and if you will use my "logic", remember to use sorted/hashed tables and not standard ones (if your tables are too big) to improve performance.

Message was edited by:

Vicenç Lozano

Re: Delete no duplicate records from internal table

Former Member — Thu, 20 Sep 2007 10:32:48 GMT

hi,

u can use this logic.................

data : temp like tablename-field1,

temp2 like tablename-field1,

tabix type i .

sort itab by fieldname.

loop at itab

if temp2 ne itab1-fieldnamevalue.

temp = itab-fieldnamealue.

else.

countinue.

endif.

tabix = sytabix + 1.

read itab index tabix.

if sy-subrc = 0.

if temp = itab-fieldnamevalue.

temp2 = itab-fieldnamevalue.

countine.

else.

delete itab.

temp2 = ''.

end if.

endif.

i tried pls do it........

regards,

karthik

Message was edited by:

karthikeyan sukumar

Re: Delete no duplicate records from internal table

VXLozano — Thu, 20 Sep 2007 10:34:42 GMT

Then you will must to find an algorythm to do it, as I said you in my first post.

By the way, for the next time you will need to clarify a question, use a reply, because lots of people do not see/look when a post has been modified once the flood of (wrong?) answers start.

Use the bold button or caps* the next time (as I did) to show you want the <b>NOT</b> duplicated ones, and (maybe) people will notice what do you want really

if you will use caps, please use them just for the word you want to emphasize, a message all in caps is unreadable and is like shouting in the net

Re: Delete no duplicate records from internal table

Former Member — Thu, 20 Sep 2007 10:39:48 GMT

Hi,

Use the below code.

data : begin of itab occurs 0,

num1 type i,

num2 type i,

end of itab.

data wa like itab.

data: v_cnt type i,

v_num type i.

itab-num1 = 1.

itab-num2 = 1.

append itab.

clear itab.

itab-num1 = 1.

itab-num2 = 3.

append itab.

clear itab.

itab-num1 = 2.

itab-num2 = 4.

append itab.

clear itab.

itab-num1 = 4.

itab-num2 = 4.

append itab.

clear itab.

itab-num1 = 3.

itab-num2 = 1.

append itab.

clear itab.

itab-num1 = 3.

itab-num2 = 2.

append itab.

clear itab.

sort itab by num1.

loop at itab.

if v_num <> itab-num1.

v_num = itab-num1.

if v_cnt = 1.

delete itab where num1 = wa-num1.

clear v_cnt.

v_cnt = v_cnt + 1.

else.

clear v_cnt.

endif.

v_cnt = v_cnt + 1.

else.

v_cnt = v_cnt + 1.

endif.

move itab to wa.

endloop.

if v_cnt = 1.

delete itab where num1 = wa-num1.

endif.

loop at itab.

write:/5 itab-num1,

20 itab-num2.

endloop.

Re: Delete no duplicate records from internal table

Former Member — Thu, 20 Sep 2007 10:45:38 GMT

Check the following code, it keeps duplicate enteries and deletes single enteries.

DATA:

BEGIN OF ITAB OCCURS 0,

NAME(1),

TOTAL TYPE I,

END OF ITAB,

itab2 like table of itab with header line.

data: COUNT TYPE I.

ITAB-NAME = 'a'. ITAB-TOTAL = 480. APPEND ITAB.

ITAB-NAME = 'b'. ITAB-TOTAL = 470. APPEND ITAB.

ITAB-NAME = 'c'. ITAB-TOTAL = 470. APPEND ITAB.

ITAB-NAME = 'd'. ITAB-TOTAL = 470. APPEND ITAB.

ITAB-NAME = 'e'. ITAB-TOTAL = 460. APPEND ITAB.

itab2[] = itab[].

LOOP AT ITAB.

CLEAR COUNT.

LOOP AT ITAB2 WHERE NAME = ITAB-NAME AND TOTAL = ITAB-TOTAL.

COUNT = COUNT + 1.

ENDLOOP.

IF COUNT EQ 1.

DELETE ITAB.

ENDIF.

ENDLOOP.

LOOP AT ITAB.

WRITE:/ ITAB-NAME,ITAB-TOTAL.

ENDLOOP.

Reward points if useful, get back in case of query...

Cheers!!!

Re: Delete no duplicate records from internal table

Former Member — Thu, 20 Sep 2007 10:48:43 GMT

hi,

i tested with example.....plz check this....

data : begin of itab occurs 0,

num type i,

end of itab.

data : temp like itab-num,

temp2 like itab-num,

tabix type i .

ITAB-NUM = 1.

APPEND ITAB.

ITAB-NUM = 2.

APPEND ITAB.

ITAB-NUM = 3.

APPEND ITAB.

ITAB-NUM = 4.

APPEND ITAB.

ITAB-NUM = 4.

APPEND ITAB.

itab-num = 3.

append itab.

itab-num = 4.

append itab.

itab-num = 2.

append itab.

sort itab by num.

loop at itab.

if temp2 ne itab-num.

temp = itab-num.

else.

continue.

endif.

tabix = sy-tabix + 1.

read table itab index tabix.

if sy-subrc = 0.

if temp = itab-num.

temp2 = itab-num.

continue.

else.

delete itab.

temp2 = ''.

endif.

endloop.

loop at itab.

write 😕 itab-num.

endloop.

regards,

karthik.

Re: Delete no duplicate records from internal table

Former Member — Thu, 20 Sep 2007 10:53:54 GMT

Hi,

Here is the simple logic for ur problem:

*If itab1 contains ur whole data

data:

wa like line of itab1.

sort itab1.

loop at itab1.

if wa ne itab1.

delete itab1.

endif.

wa = itab1.

clear itab1.

endloop.

Regards,

Rama chary.Pammi

Re: Delete no duplicate records from internal table

Former Member — Thu, 20 Sep 2007 11:10:02 GMT

try out this logic...

data : cnt type i value 1.

cnt1 type i.

data : begin of itab occurs 0,

num type i,

end of itab.

enter some values into internal table and then.

sort itab by num.

describe table itab.

cnt1 = sy-tfill.

loop at itab.

temp = itab-num.

while ( cnt <= cnt1 ).

read table itab index cnt.

if itab-num ne temp.

delete itab.

endif.

cnt = cnt + 1.

endwhile.

endloop.

Message was edited by:

Naveen George

Message was edited by:

Naveen George

Re: Delete no duplicate records from internal table

Former Member — Fri, 21 Sep 2007 08:48:39 GMT

Hi this program provides the functionality u want.......

data : begin of itab occurs 0,

num1 type i,

num2 type i,

end of itab.

data : begin of itab1 occurs 0,

num1 type i,

cnt type i,

end of itab1.

data : v_flag type c.

data : cnt type i.

data : v_tabix like sy-tabix.

itab-num1 = 1.

itab-num2 = 2.

append itab.

clear itab.

itab-num1 = 1.

itab-num2 = 2.

append itab.

clear itab.

itab-num1 = 1.

itab-num2 = 2.

append itab.

clear itab.

itab-num1 = 2.

itab-num2 = 5.

append itab.

clear itab.

itab-num1 = 3.

itab-num2 = 5.

append itab.

clear itab.

itab-num1 = 3.

itab-num2 = 7.

append itab.

clear itab.

itab-num1 = 4.

itab-num2 = 6.

append itab.

clear itab.

sort itab by num1.

loop at itab.

at new num1.

clear cnt.

cnt = cnt + 1.

v_flag = 'X'.

endat.

if not v_flag = 'X'.

cnt = cnt + 1.

else.

clear v_flag.

endif.

at end of num1.

itab1-num1 = itab-num1.

itab1-cnt = cnt.

append itab1.

clear : itab1 , cnt.

endat.

endloop.

delete itab1[] where cnt ne 1.

loop at itab.

v_tabix = sy-tabix.

read table itab1 with key num1 = itab-num1.

if sy-subrc = 0.

delete itab index v_tabix.

endif.

endloop.

loop at itab.

write 😕 itab-num1 , itab-num2.

endloop.

Please Reward points if useful.

Regards

Vasu

Re: Delete no duplicate records from internal table

Former Member — Fri, 21 Sep 2007 08:57:51 GMT

Hi,

sort it_table by the record no.s and take one variable

then loop at it_table into wa.

if sy-tabix = 1.

wa_temp = wa-record1.

endif.

if wa_temp <> wa_record.

delete it_table where record = wa_record.

endif.

endloop.

Re: Delete no duplicate records from internal table

Former Member — Fri, 21 Sep 2007 10:19:03 GMT

Hi Amit,

This might help u.

Itab1 = itab2.

delete adjacent duplicates itab2.

loop at itab2.

read table itab1 with key = itab2-key.

if sy-subrc eq 0.

n = sy-tabix.

n = n + 1.

read table itab1 index n.

if sy-subrc eq 0 and itab1-key = itab2-key.

else.

delete itab1.

endif.

endloop.

Re: Delete no duplicate records from internal table

Former Member — Mon, 11 May 2009 21:41:54 GMT

Thanks every one!!!