topic Re: Find row differences in Discussions

Find row differences

patriciafm811 — Mon, 18 Nov 2024 13:15:30 GMT

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)

Re: Find row differences

mmarchandFSLR — Mon, 18 Nov 2024 14:25:43 GMT

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

Re: Find row differences

SophieCuvillier — Mon, 18 Nov 2024 14:43:00 GMT

Hello,

To get the difference between 2 rows, then you have the formula below.

If you want to make sure that your line is at least 10 away from all the other lines, you can do this as follows:

- Convert your column into a number (see what we do with characters)
- Arrange your column in ascending order
- Create the Diff column using the formula above and check if there are some diff < 10

Re: Find row differences

patriciafm811 — Mon, 18 Nov 2024 14:40:59 GMT

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

Re: Find row differences

patriciafm811 — Mon, 18 Nov 2024 14:44:49 GMT

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

SophieCuvillier — Mon, 18 Nov 2024 14:51:33 GMT

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

txnelson — Mon, 18 Nov 2024 15:40:24 GMT

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

mmarchandFSLR — Mon, 18 Nov 2024 16:16:35 GMT

I was talking about these:

Re: Find row differences

patriciafm811 — Mon, 18 Nov 2024 18:45:01 GMT

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

jthi — Mon, 18 Nov 2024 20:13:19 GMT

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)))