08-04-2013 10:40 AM
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 ?
08-04-2013 1:11 PM
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
08-04-2013 1:18 PM
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.
08-04-2013 1:32 PM
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 .
08-04-2013 1:37 PM
08-04-2013 1:45 PM
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.
08-04-2013 3:54 PM
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
08-04-2013 11:23 PM
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
08-05-2013 2:07 AM
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.
08-05-2013 6:09 AM
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.
08-05-2013 10:57 AM
08-05-2013 11:20 AM
08-05-2013 11:53 AM
I just don't know how to display lit_data_1 after execution. Sorry for these silly questions, still a beginner :$
11-22-2013 10:57 AM
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
08-05-2013 6:39 AM
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
08-05-2013 11:55 AM
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
08-05-2013 12:02 PM
Thanks for answering. The code does not show anything after execution, no output/table.
08-05-2013 1:11 PM
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
08-05-2013 2:37 PM
Hi Mr. Babl,
You are right about KNB1. The code works just great. Thank you very much for your help.
Best regards,
Shkelqim
08-05-2013 5:54 PM
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
11-22-2013 9:49 AM
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.