SAP Query Remove Duplicate Records

[former_member355038]

Hi

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

[former_member355038]

Hi

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

[Former Member]

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

[Former Member]

This message was moderated.

[former_member355038]

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

[Former Member]

In order to compile your code you'll need to use "dynamic" references to get the sort and "delete adjacent duplicates" to work - here's a simple example where two copies of the T001 table are loaded into GT_T001, and the subroutine "logic" then eliminates:the duplicates (note: replace the { } brackets shown with the usual field-symbol angle brackets) :

report zlocal_jc_dynamic_sort_del. data:   gt_t001               type standard table of t001. start-of-selection.   select * into table gt_t001     from t001.   select * appending table gt_t001  "create some dupes     from t001.   perform logic. *&---------------------------------------------------------------------* *&      Form  logic *&---------------------------------------------------------------------* form logic.   field-symbols:     {lfst_table}       type any table.   assign ('GT_T001') to {lfst_table}.   describe table {lfst_table}.   write: / 'Lines =', sy-tfill.   sort {lfst_table} by ('BUTXT').   delete adjacent duplicates from {lfst_table} comparing ('BUTXT').   describe table {lfst_table}.   write: / 'Lines =', sy-tfill. endform.                    "logic 

Jonathan

[jogeswararao_kavala]

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

[former_member355038]

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

[jogeswararao_kavala]

May try with Comparing <dtab>-QMNUM

[Former Member]

Di you declare your table with header line?

[former_member355038]

How do I declare the table with a header line?

[former_member355038]

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.

[Former Member]

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.

[jogeswararao_kavala]

Try

Sort <dtab> by <dtab>-qmnum.

[former_member355038]

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

Get the following:

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

[Former Member]

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

Something like this.

itab TYPE STANDARD TABLE OF typetable WITH HEADER LINE.

[former_member355038]

No, I have following:

FIELD-SYMBOLS <dtab> TYPE standard TABLE.

[Former Member]

add to the sentence the following. WITH HEADER LINE.

[former_member355038]

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.

[Former Member]

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.

[former_member355038]

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

[Former Member]

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.

[former_member355038]

Hi

I tried to add in the line into the infoset:

data %dtab type standard table of /1BCDWB/IQ000000000167 with header line into the infoset data section but now the  query has a short dump.

In the Infoset the DATA section:

FIELD-SYMBOLS <dtab> TYPE standard TABLE.

Record Processing section:

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

Sort <dtab>.

DELETE ADJACENT DUPLICATES FROM <dtab> comparing all fields.

[Former Member]

Have you tried using dynamic field names as I suggested above i.e. just change your "sort <dtab>" to "sort <dtab> by ('QMNUM')." - and use "delete adjacent duplicates from <dtab> comparing ('QMNUM')."...?

Jonathan

[former_member355038]

Hi,

Thanks for the info on the dynamic field names, I put in the sort as you have suggested but still I get a duplicate entry on the report.

If I enter only 1 notfication in the selection screen I get a duplicate record on the output screen:

eg,

Notifcation 1 £100

Notifcation 1 £100

if I enter a range of 4 notifcations the firit 3 are ok 1 record for each line but the last record is duplicated:

eg,

Notifcation 1 £100

Notifcation 2 £200

Notifcation 3 £300

Notifcation 4 £100

Notifcation 4 £100

Do I need some kind of LOOP statement in my code?

thanks

Joe

[Former Member]

Hi Joe,

Try below steps.

We have lists of events in 'Code section'.

In Data Section, declare an internal table

FIELD-SYMBOLS <fs_dtab> TYPE STANDARD TABLE.
DATA: sort_f1 TYPE fieldname.

In INITIALIZATION Event write the following code.

sort_f1 = 'QMNUM’.

In END-OF-SELECTION (after list) Event write the following code.

ASSIGN ('%G00[]') TO <fs_dtab>.
IF <fs_dtab> IS ASSIGNED.
SORT <fs_dtab> BY (sort_f1)
ASCENDING.
DELETE ADJACENT DUPLICATES FROM <fs_dtab> COMPARING  (sort_f1).

or

DELETE ADJACENT DUPLICATES FROM <fs_dtab> COMPARING all fields (based on requirement)

ENDIF.

Regards,

Manasa Veena P.

[former_member355038]

Hi Manasa,

I came across the following issue when in INITIALIZATION Event write the following code.

sort_f1 = 'QMNUM’.

error message:

"Literals that take up more than one line are not permitted"

[Former Member]

Check whether you have any literal quoted with ' (single quote)

eg: sort_f1 = ’QMNUM .

it should be sort_f1 = ’QMNUM’.