How to find duplicated records in KNA1?

[Former Member]

    Dear SAP Professionals,

I have an assignment in SAP which I am trying to solve, but so far I did not managed to, as a beginner in SAP and ABAP.

I need to check if there are duplicated records in the address data base.

The customer are stored in the following tables:

KNA1 Master data with address, global view

KNB1 Master data for company code

We were filling into the field KNB1-ALTKN the previous number.

Now the idea is to find all duplicated records with this number, but there are only duplicated records if the KUNNR is different. Because the same customer can be in several company codes (BUKRS), there are for sure more than one entry with the same ALTKN.

So the condition here is : different KUNNR with the same ALTKN means duplicated record.

Can some one please help me do this ?

[Former Member]

Hi

i suppose that you can not download knb1-altkn to an excel , filter from duplicates and then paste it back to an se16 against kna1,can you?

Does it make sense?

Let me know

a

[Former Member]

Hi Andrea,

Thank you for your answer. This is an assignment given to me, so I have to write the code that checks for the duplicates and make an output showing them. I don't think that using excel would count as a solution.

[JL23]

As this was posted in an ABAP forum, I believe that this problem should be solved with a bit ABAP coding instead of going thrugh Excel, isn't it?

I will not tell my self an ABAPer, maybe a lousy Abaper, but you could for example select all entries from KNA1 which have the same old number in KNB1-ALTKN into an enternal table and delete the adjacent duplicates. if more than 1 record remains, then you found actually a duplicate and you could write it to the output of that report.

But to be honest, real duplicates are not identified just by equal old numbers.

A ship-to customer has contrary to a sold-to customer no company code data view, hence no old number to identify a duplicate.

What is a duplicate? same name, same address, same telephone number?

is Köln and Koeln the same? is Abbey Road and Abbey Rd. the same?

to really identify duplicates you probably need to support from humans. Need a report that uses Fuzzy search and lists potential duplicates and the user decides which one is a real duplicate .

[Former Member]

yeah probably it was a laizy answer...

[JL23]

Excel is an option, depends a bit on the volume and how often you want to do such analysis.

if it is one-time thing on one field, then Excel would be my first choice too.

[Former Member]

Hi Jurgen,

Thank you for your answer. In my case, different KUNNR with the same ALTKN means duplicated record.

I've been trying this, but it says: Statement concluding with kna1 ..ended unexpectedly (kna1_TEMP = kna1.).

     DATA: kna1_TEMP LIKE kna1 OCCURS 0 WITH HEADER LINE.      DATA: kna1_DUPLICATE LIKE kna1 OCCURS 0 WITH HEADER LINE.      select options kunnr for KNa1-ALTKN        kna1_TEMP = kna1.        SORT kna1_TEMP BY kunnr.      DELETE ADJACENT DUPLICATES FROM kna1_TEMP COMPARING Kunnr.        LOOP AT kna1_TEMP.          LOOP AT kna1 WHERE kunnr = kna1_TEMP-kunnr.          IF SY-TABIX > 1.            APPEND kna1 TO kna1_DUPLICATE.                    ENDIF.        ENDLOOP.     endselect.       ENDLOOP.

Thanks

[JL23]

There is something totally wrong

KNA1 does not have a field ALTKN

you are completely missing KNB1

you should have an internal table with KNA1-KUNNR and KNB1-ALTKN

[Former Member]

O man . Can you do some part of the code for me, at least the declaring of values, so I can have something to start with?   Thank you very much for your help Jurgen.

[Former Member]

Hello Please try as below,

select kunnr and altkn.

sort the table via altkn.

Tehn loop the table and compare the entries one by one. If altkn matches pass the record in another final internal table

TYPES : BEGIN OF lty_data,

          kunnr TYPE kna1-kunnr,

          altkn TYPE knb1-altkn,

    END OF lty_data.

DATA: lv_altkn   TYPE knb1-altkn,

       lit_data   TYPE STANDARD TABLE OF lty_data WITH HEADER LINE,

       lit_data_1 TYPE STANDARD TABLE OF lty_data.

SELECT a~kunnr b~altkn INTO TABLE lit_data FROM kna1 AS a INNER JOIN knb1 AS b ON a~kunnr EQ b~kunnr.

SORT lit_data BY altkn.

LOOP AT lit_data.

   IF lit_data-altkn = lv_altkn.

     APPEND lit_data TO lit_data_1.

   ENDIF.

   lv_altkn = lit_data-altkn.

   CLEAR lit_data.

ENDLOOP.

The final table lit_data_1 will have your desired records.

[JL23]

Have you considered to take a basic training someday?

[Former Member]

Shkelqim Gerxhaliu

did it worked ?

[Former Member]

I just don't know how to display lit_data_1 after execution. Sorry for these silly questions, still a beginner :$

[Former Member]

Hey Shkelqim,

DATA: lwa_data like line of lit_data_1.

loop at LIT_DATA_1 into lwa_data.

write: lwa_data-kunnar, lwa_data-altkn.

endloop.

Regards,

Bhaskar

[former_member1329]

Hello Shkelqim Gerxhaliu

you  can do it using  KNB1 table itself.

Select  KUNNR ALTKN from KNB1 into IT_KNB1.

Sort IT_KNB1 BY ALTKN

DELETE ADJACENT DUPLICATES From IT_KNB1 COMPARING ALTKN.

LOOP AT IT_KNB1

Select Count (*) into  LV_Count from KNB1 Where ALTKN = IT_KNB1-ALTKN.

If  LV_Count >1, then

Display all values from KNB1 for this IT_KNB1-ALTKN.

You can either select KNB1 Again or use another temp table  IT_KNB1_TEMP[] = IT_KNB1 before deleting adjacent Duplicates

[former_member195402]

Hi,

please check this:

REPORT  ztestaltkn.

TYPES: BEGIN OF x_knb1,

         altkn TYPE knb1-altkn,

         kunnr TYPE knb1-kunnr,

       END   OF x_knb1.

DATA: xs_knb1  TYPE x_knb1.

DATA: xt_knb1  TYPE TABLE OF x_knb1.

DATA: xv_kflag TYPE flag.

DATA: xv_kunnr TYPE kunnr.

START-OF-SELECTION.

* Distinct selection to eliminiante BUKRS duplicates

  SELECT DISTINCT altkn kunnr FROM knb1

                              INTO  TABLE  xt_knb1

  WHERE  altkn                  NE  space.

* SORT

  SORT xt_knb1                  BY  altkn kunnr.

* LOOP

  LOOP                          AT  xt_knb1

                              INTO  xs_knb1.

*   New ALTKN

    AT  NEW                         altkn.

      CLEAR                         xv_kunnr.

      CLEAR                         xv_kflag.

    ENDAT.

*   1st KUNNR

    IF  xv_kunnr                IS  INITIAL.

      xv_kunnr                   =  xs_knb1-kunnr.

      CONTINUE.

    ENDIF.

*   List 1st KUNNR

    IF  xv_kflag                IS  INITIAL.

      SKIP.

      WRITE:  /1                    xs_knb1-altkn,

                                    xv_kunnr.

      xv_kflag                   =  'X'.

    ENDIF.

*   List duplicates

    WRITE:  /1                      xs_knb1-altkn,

                                    xs_knb1-kunnr.

* DONE

  ENDLOOP.

Regards,

Klaus

[Former Member]

Thanks for answering. The code does not show anything after execution, no output/table.

[former_member195402]

Hi,

I've checked this sample program on our system and it was working fine.

If nothing is shown, there ain't be any KNB1 entry in your system where ALTKN has a value.

Please check this in SE16.

Regards,

Klaus

[Former Member]

Hi Mr. Babl,

You are right about KNB1. The code works just great. Thank you very much for your help.

Best regards,

Shkelqim

[former_member183155]

Hi;

You create a query with SQVI , create a program. You know that tables are related , joined them .

After that you can assign it to a tcode with se93. you will have a vendor list program. It is so simple for me try it.

M.Ozgur Unal

[Former Member]

Hi everyone,

I have an assignment in SAP which I am trying to solve, but so far I did not managed to, as a beginner in SAP and ABAP.

I need to check if there are duplicated records in the address data base.

The customer are stored in the following tables:

KNA1 Master data with address, global view

KNB1 Master data for company code

We were filling into the field KNB1-ALTKN the previous number.

Now the idea is to find all duplicated records with this number, but there are only duplicated records if the KUNNR is different. Because the same customer can be in several company codes (BUKRS), there are for sure more than one entry with the same ALTKN.

So the condition here is : different KUNNR with the same ALTKN means duplicated record.

Can some one please help me do this ?

Thank you in advance.