813711_en-US

liliconcom

Find row differences

Hello, 

I am working on a file and I need to determine if any of the numbers in the 'Pos UCN' column are within 10 of any other row in that column. For example, rows 6344 and 6345 are within 10 of each other. How do I identify that? I know that this will create a new column, which is what I want. I have spent 2 days on the user community trying various things and have not had any luck. 

 

 

the difference between the rows. I believe this would creat a new column of 'difference' between the. I would also like to identify (in another new column)

Basic Data Analysis and ModelingWindowsRe: Find row differences

What do you want to do with the values that contain characters?

Re: Find row differences

Hello,

 

If this is the difference between two consecutive lines, then you have the formula below

 

 

Re: Find row differences

They have to be character values because I need the leading 0's. 

Re: Find row differences

Thank you. This gives me a count difference between rows. Now how do I highlight rows that a difference of less than 10?

Re: Find row differences

In the same way, you can create a formula as below to get if the difference is less than 10 (1) or not (0) in a new column

 

 

And use color values property to highlight rows with less than 10 of difference

 

 

 

Re: Find row differences

You can also set the rowstate to selected for the rows you find that have met the criteria

dt << select rows(.....)

Re: Find row differences

I was talking about these:

 

 

Re: Find row differences

So I was able to make my two additional columns (Diff and Prev UCN <=5). But I want to be able to get both the numbers. So for row 6345, it identifies as a previous row within 5, but I want to also identify that row before (6344)? This is looking for a "Pos UCN" +/- 5 to see if there are numbers that are close together. If they are close together, I want to identify both so I can look further and determine any potential issues. 

Re: Find row differences

One option is to change the Diff column to perform the comparison

If(Dif(Num(:Pos UCN), 1) <= 5 | Abs(Dif(Num(:Pos UCN), -1)) <= 5,
	1,
	0
)

Or just calculate minimum difference and then perform check after

Min(Dif(Num(:Pos UCN), 1), Abs(Dif(Num(:Pos UCN), -1)))