topic Find time difference between rows in Discussions

Find time difference between rows

patriciafm811 — Fri, 07 Feb 2025 15:54:11 GMT

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

Re: Find time difference between rows

jthi — Fri, 07 Feb 2025 16:10:45 GMT

~~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.~~

Re: Find time difference between rows

jthi — Fri, 07 Feb 2025 16:28:25 GMT

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)

Re: Find time difference between rows

patriciafm811 — Fri, 07 Feb 2025 16:41:41 GMT

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

Re: Find time difference between rows

jthi — Fri, 07 Feb 2025 16:52:34 GMT

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)

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)
Then you want values which are at maximum 30 minutes apart while filling rule 1
Center codes?
Anything else?

Re: Find time difference between rows

patriciafm811 — Fri, 07 Feb 2025 17:24:33 GMT

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.

Re: Find time difference between rows

jthi — Fri, 07 Feb 2025 17:48:31 GMT

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

Re: Find time difference between rows

patriciafm811 — Fri, 07 Feb 2025 17:52:46 GMT

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
)

Re: Find time difference between rows

patriciafm811 — Fri, 07 Feb 2025 17:55:50 GMT

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

Re: Find time difference between rows

patriciafm811 — Fri, 07 Feb 2025 18:00:51 GMT

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