cancel
Showing results for 
Search instead for 
Did you mean: 

Compare two values from the same column

0 Kudos

I would like to get an indication of 1 or 0 every time a number is repeated in the same column (after I have sorted the column)

How can I get such an indication?

AlexGourdet
Community Manager
Community Manager
0 Kudos

Thank you for visiting SAP Community to get answers to your questions.

Since you're asking a question here for the first time, I'd like to recommend you with the following steps so you can get the most out of your community membership:

I also recommend that you include a profile picture. By personalizing your profile, you encourage readers to respond: https://developers.sap.com/tutorials/community-profile.html.

I hope you find this advice useful, and we're happy to have you as part of SAP Community!

All the best,
-Alex

JesperB1
Advisor
Advisor

Hello מיכל דוד,

Is this column the result of a query?

Thanks,

Jesper

0 Kudos

Hey Jesper,

Yes it is.

Accepted Solutions (1)

Accepted Solutions (1)

Johan_H
Active Contributor

Hi מיכל דוד,

If you are running MS SQL Server 2012 or higher, you can use the LAG function. For example:

select ItemCode AS [The ItemCode on this row]
 ,lag(ItemCode, 1, 'There is no previous row') over (order by ItemCode) AS [The ItemCode on the previous row]
 from OITM<br>

or

SELECT x.[curr] AS [ItemCode]
 ,CASE
   WHEN x.[curr] = x.[prev] THEN 1
   ELSE 0
  END AS [1 = same, 0 = different]
 FROM (select ItemCode as [curr]
      ,lag(ItemCode, 1, 'There is no previous row') over (order by ItemCode) as [prev]
 from QUT1) x

Regards,

Johan

Answers (2)

Answers (2)

jitin_chawla
Advisor
Advisor

Hi,

If you want to do it in Excel you can use the formula as in example below:

Make a new column as Number of Occurrence and then in the first row of the value field, amend the Formula as per your Column and row number. This will list the number of occurrence of each number in the column list.

Kr,

Jitin

0 Kudos

Hello Jitin,

Thanks for your answer, but I am trying to do so at the Sap system, without downloading it to Excel.

Is it possible?

jitin_chawla
Advisor
Advisor
0 Kudos

Hi,

Please update the query so that we can check here.

Kr,

Jitin

JesperB1
Advisor
Advisor
0 Kudos

Hello,

You can use Case with Count - on this link is an example https://stackoverflow.com/questions/17975229/using-sql-count-in-a-case-statement

I hope that helps,

Jesper

gonzalogomez
Active Contributor
0 Kudos
SELECT T0.[Docnum], ROW_NUMBER() OVER(PARTITION BY T0.DocNum ORDER BY T0.DocNum Asc) as NVeces 
FROM ORDR T0 order by t0.DocNum