Using Row_number to handle Duplicate in AMDP
Data is a part of important in ERP system. But, when you don’t have a specific key in the dataset, retrieving data from multiple tables is more problems, such as duplicate row.
In ABAP coding, we can easily resolve it by using DELETE ADJACENT DUPLICATES in internal table. But how to do that in AMDP?
Luckily, SAP supported build-in functions to resolve this problem, that is ROW_NUMBER in WINDOW FUNCTION technique. So, how to use it? Let’s get started!!!
Assume that we can two datasets, one for debit and one for credit:
Join two datasets, we will have new dataset follow by image
But I want remove duplicate value by key (BUKRS, GJAHR, BELNR, BUZEI_DEB and HKONT_DEB). And, how to implement that in AMDP method
Step 1: Join two datasets: debit and credit and store it into temp_data.
Note that: I prepared data previously in ABAP program, and i will debug inside AMDP to see data
Step 2: Using ROW_NUMBER to number sequential number for each row followed by a partition (BUKRS, GJAHR, BELNR, BUZEI_DEB, and HKONT_DEB)
Then, you will see dataset will appear a new field (RN_DEB) and each record will number follow by partition/group (start number = 1 for each partition)
With ORDER BY statement, data will be sorted based on specific fields which I assigned in this code
Step 3: Remove duplicate in dataset
Get data from temp_remove_data and filter it by fetching only records that RN_DEB = 1, then store it into TEMP_DATA dataset
Congratulation, you removed duplicate data successfully.
By using the power in window function, specifically in this article which is ROW_NUMBER. I hope it will provide a solution/ ways to resolve duplicate in the dataset
Hope this help
Thanks for your attention