topic Re: SAP Query Remove Duplicate Records in Application Development and Automation Discussions

SAP Query Remove Duplicate Records

former_member355038 — Thu, 22 May 2014 20:37:40 GMT

I have query which has a number of duplicate records and I have tried to remove them using the following code:

Record processing of infoset:

================================

ASSIGN ('%dtab[]') TO <dtab>.

DELETE ADJACENT DUPLICATES FROM <dtab> comparing all fields.

==========================================================

Before I added the above I had 3 identical records, now I have only got 2 but I only want 1 record showing therefore I assume I need a LOOP statement? how should I adjust the above code to include the LOOP statement to check all the records in the dtab table?

thanks

Joe

Re: SAP Query Remove Duplicate Records

Former Member — Fri, 23 May 2014 03:24:14 GMT

Were there other records in dbtab other than the three duplicates records? If so, you might need to include a "sort dbtab." before the "delete adjacent.." to ensure the three rows are next to each other... also check that every column of those three rows match - if not, you can "delete adjacent ... comparing x y z" to compare the key values.

Jonathan

Re: SAP Query Remove Duplicate Records

Former Member — Fri, 23 May 2014 08:47:19 GMT

This message was moderated.

Re: SAP Query Remove Duplicate Records

former_member355038 — Fri, 23 May 2014 09:24:33 GMT

Hi I have now changed the code to:

============

ASSIGN ('%dtab[]') TO <dtab>.

sort <dtab>.

DELETE ADJACENT DUPLICATES FROM <dtab>

comparing qmnum.

=====================

But Iwhen saving the codeI get the message:

The specified type has no structure and therefore no component called "QMNUM". component called "QMNUM".

I want to compare the field QMNUM in dtab and only return 1 record when there are multiple records.

thanks for your help

Joe

Re: SAP Query Remove Duplicate Records

jogeswararao_kavala — Fri, 23 May 2014 13:27:48 GMT

Hi Joe,

Your 'Delete Adjacent duplicates .... comparing all fields' syntax was removing only one record instead of 2 out of 3 lines.... this can be most probably due to the rest of the 2 lines are having atleast one field value not equal. (Hope you've understood).

I suggest you to try the CHECK syntax. For this you need to identify the fields which have different values and identify the value which you desire. For example the field is ABCKZ, then give this line in the Record Processing section.

CHECK VIQMEL-ABCKZ = 'A' .

(Assuming that you've used VIQMEL table)

Jogeswara Rao K

Re: SAP Query Remove Duplicate Records

former_member355038 — Fri, 23 May 2014 13:54:56 GMT

Hi I understand 1 field is different to the other 2 therefore I want to use a COMPARE statement to delete the records based on QMNUM field(same in all 3 rows):

============

ASSIGN ('%dtab[]') TO <dtab>.

sort <dtab>.

DELETE ADJACENT DUPLICATES FROM <dtab>

comparing qmnum.

=====================

But I when saving the code I get the message:

The specified type has no structure and therefore no component called "QMNUM". component called "QMNUM".

Do I have to do something with the data definition?

thanks

Joe

Re: SAP Query Remove Duplicate Records

jogeswararao_kavala — Fri, 23 May 2014 14:26:23 GMT

May try with Comparing <dtab>-QMNUM

Re: SAP Query Remove Duplicate Records

Former Member — Fri, 23 May 2014 14:31:18 GMT

Di you declare your table with header line?

Re: SAP Query Remove Duplicate Records

former_member355038 — Fri, 23 May 2014 14:49:33 GMT

How do I declare the table with a header line?

Re: SAP Query Remove Duplicate Records

former_member355038 — Fri, 23 May 2014 14:54:45 GMT

I checked the records and the query is deleting all duplicates apart for the last record, so for example I have 10 records and 5 are duplicates the query removes 4 but always leaves the last one, is there a LOOP statement that needs to go in there? current code is:

ASSIGN ('%dtab[]') TO <dtab>.
sort <dtab>.
DELETE ADJACENT DUPLICATES FROM <dtab> comparing all fields.

Re: SAP Query Remove Duplicate Records

Former Member — Fri, 23 May 2014 15:03:04 GMT

Do you know the fields that probably are the same, don't you? so try this

SORT itab by [ASCENDING/DESCENDING] FLD1 [ASCENDING/DESCENDING] FLD2.

DELETE ADJACENT DUPLICATES FROM itab comparing FLD1 FLD2.

Re: SAP Query Remove Duplicate Records

jogeswararao_kavala — Fri, 23 May 2014 15:04:49 GMT

Try

Sort <dtab> by <dtab>-qmnum.

Re: SAP Query Remove Duplicate Records

former_member355038 — Fri, 23 May 2014 15:23:36 GMT

Tried Sort <dtab> by <dtab>-qmnum.

Get the following:

"<DTAB>" is a table without a header line and therefore has no component called "QMNUM".

Re: SAP Query Remove Duplicate Records

Former Member — Fri, 23 May 2014 15:28:03 GMT

Have you declare your internal table with WITH HEADER LINE,?

Something like this.

itab TYPE STANDARD TABLE OF typetable WITH HEADER LINE.

Re: SAP Query Remove Duplicate Records

former_member355038 — Fri, 23 May 2014 15:33:05 GMT

No, I have following:

FIELD-SYMBOLS <dtab> TYPE standard TABLE.

Re: SAP Query Remove Duplicate Records

Former Member — Fri, 23 May 2014 15:35:24 GMT

add to the sentence the following. WITH HEADER LINE.

Re: SAP Query Remove Duplicate Records

former_member355038 — Fri, 23 May 2014 15:58:51 GMT

I have tried to change it to:

FIELD-SYMBOLS <dtab> TYPE standard TABLE WITH HEADER LINE.

But I get the message:

"." expected after "TABLE"

and when I add the "." I get the message:

The statement "WITH" is not designated.

Re: SAP Query Remove Duplicate Records

Former Member — Fri, 23 May 2014 16:07:39 GMT

don't you have a type for your table?

types: begin of type1,

campo1(10),

campo2 type i,

end of type1.

itab type standard table of type1 with header line.

if you want to assing it to a field-symbol would be as follows:

field-symbols: <dtab> like itab.

Re: SAP Query Remove Duplicate Records

former_member355038 — Fri, 23 May 2014 16:15:15 GMT

I am using SQ02 to add code to the infoset, when I check the query via SE38, the following code exists:

data %dtab type standard table of /1BCDWB/IQ000000000167 with header line

Re: SAP Query Remove Duplicate Records

Former Member — Fri, 23 May 2014 16:23:03 GMT

So, you can order the table %dtab[] using the fields you know are the same.

SORT %dtab[] by [ASCENDING/DESCENDING] FLD1 [ASCENDING/DESCENDING] FLD2.

DELETE ADJACENT DUPLICATES FROM itab comparing FLD1 FLD2.