Find time difference between rows

[#1]

Hello, 

I am working on setting up a script. One of the steps is for us to determine if the 'Timestamp' is within 30 minutes of the row above or below, with them in sequential order by UCN. Obviously, I need this to include the date and time. Below is the script/formula I am using. But when I do it, it is only looking at times and not taking into account the UCN or the date. You can see in my '30 minutes' column that it is not identifying right. 

This is set up so that the UCNs are those within 5 digits of each other. But then I need it to determine if those UCN's have a timestamp within 30 minutes of the ones that are within the 5 digits. Hopefully that makes sense. So if you look at rows 12 and 13, it says they are a match based on the timestamp, but they are not because they have different center codes and the UCNs are not 5 digits apart. Row 11 and 12 are within 5 digits for the UCN, but the time is more than 30 minutes apart, so it should not be identifying as a '1'/red in the 'time within 30' column. 

// New column: Time within 30
Data Table("Time within 30 minutes") << New Column("Time within 30",
	Numeric,
	"Continuous",
	Format("Best", 12),
	Formula(
		If(
			Dif(Num(:Unit Entry Timestamp), 1) <= 30 | Abs(Dif(Num(:Unit Entry Timestamp), -1))
			 <= 30,
			1,
			0
		)
	)
) << Move Selected Columns({:Time within 30}, after(:Positive or Negative));
 
// Change column info: Time within 30
Data Table("Time within 30 minutes"):Time within 30 <<
Set Property("Value Colors", {0 = 2, 1 = 3}) << Color Cell by Value(1);

[#2]

Create new column in which you combine date and time (date + time) and then fix your Pos UCN to numeric if they are numbers. Then you should be able to use similar idea you have for your time comparison to Pos UCN and to the new timestamp column.

[#3]

Also remember to comparisons in seconds as that is how JMP handles time. You can for example use In Minutes(30) to convert 30minutes into seconds (or just use 30*6)

[#4]

I need them to remain as character values because I need the leading 0's.

[#5]

Ok, in that case either create new numeric column for them or use Num for the conversion (of course your 5 digits apart can mean something else than the numeric difference between them).

Can you provide all the rules you have in order if importance, for example I'm not sure how center codes are related to this (you did mention them)

1. You want to find rows which have UNC values maximum difference of 5 (I assume that is what it means to be 5 digits different)
2. Then you want values which are at maximum 30 minutes apart while filling rule 1
3. Center codes?
4. Anything else?

[#6]

The rules you put are fairly correct. The center code is just the identifier for each center. The center code is the first 3 digits of the UCN if that helps. I already have it in my script and main file (which is huge) to filter down from about 12k rows to this 120. That one does the UCNs withing 5 digits. 

The next step is determining if the similar UCNs have a timestamp within 30 minutes of each other (for the same center and within 5 digits of the UCN). That is what I am trying to figure out how to do. The formula I am currently using that isn't working should be listed in the column formula option in the table I uploaded earlier. 

[#7 · Accepted solution]

Not sure if this formula is correct as it disagrees quite heavily with your formula

Current Data Table() << New Column("R", Numeric, Nominal, Formula(
	If(:Center Code == Lag(:Center Code) | :Center Code == Lag(:Center Code , -1),
		If(Min(Abs(Dif(Num(:Pos UCN))), Abs(Dif(Num(:Pos UCN), -1))) <= 5,
			Min(Abs(Dif(:Unit Entry Timestamp)), Abs(Dif(:Unit Entry Timestamp, -1))) <= In Minutes(30)
		,
			0
		)
	,
		0
	);
));

[#8]

This is the formula i have to get my within 5 column

<JSL>

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

[#9]

I just realized I hadnt included that column in my original question.. 

[#10]

This formula was EXACTLY what I needed!!!!! Thank you!!!