Skip to Content
Personal Insights
Author's profile photo Chau Nguyen Phuc

Using Row_number to handle Duplicate in AMDP

Introduction

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

Class definition

Class implementation

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.

Summary

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

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Matthew Billingham
      Matthew Billingham

      I like your blog, but do you think you could post the coding samples using the {;} button, so it can be copy pasted?

      Thanks.
      Author's profile photo Chau Nguyen Phuc
      Chau Nguyen Phuc
      Blog Post Author

      Hi,
      I will do that in future articles. But in this article, if you need my sample code, Could you please inbox to me
      Thanks so much

      Author's profile photo Matthew Billingham
      Matthew Billingham

      Just edit your blog?

      Author's profile photo Chau Nguyen Phuc
      Chau Nguyen Phuc
      Blog Post Author

      The original purpose was to provide a solution to solve this problem. So please sympathize with me. I will note it and do it step by step (both image and code to copy and paste) at new article in the future
      Thanks!!!