cancel
Showing results for 
Search instead for 
Did you mean: 

CDS view - remove entries based on duplicates

SankarKannan
Discoverer
0 Kudos

Hello experts,

I have created CDS table function like below

SankarKannan_0-1708915205223.png

Class:

SankarKannan_1-1708915270334.png

The output:

SankarKannan_2-1708915557895.png

I want to remove the highlighted entries using the subquery/CASE  or conditions in AMDP. please suggest possible options/statements in AMDP

Accepted Solutions (0)

Answers (1)

Answers (1)

AtulK
Explorer
0 Kudos

Hello Sankar,

I understand that you need to remove adjancent duplicates from your data.

For acheiving this there is ROW_NUMBER() function in SQL. attaching code for reference.

output table = 

select
             client as client ,
             bukrs AS bukrs,
             ANLN1  AS ANLN1,
             ANLN2  AS ANLN2,
             AFABE as AFABE,
             BZDAT as BZDAT,
             row_number OVER PARTITION BY clientBUKRSANLN1ANLN2AFABE
                                 ORDER BY BZDAT ASC AS rank_asc
             from faav_anep;

        select
            client as client ,
            bukrs AS bukrs,
            ANLN1  AS ANLN1,
          ANLN2  AS ANLN2,
           AFABE as AFABE,
          BZDAT as FDT
           from :output_table
             WHERE rank_asc = 1 ;