topic Re: Calculate time difference in Discussions

Calculate time difference

KST-CPT — Fri, 13 Apr 2018 15:59:45 GMT

I have a set od data, In the script I call it RTDT. It contains a Time, Sub_name and Substrate_ID columns. I am trying to calculate the time difference between the first time and last time a unique Sub_name and Substrate_ID are listed. They will be listed sequentially for each unique Sub_name and Substrate_ID. My code is as follows:

RTDT << New Column( "Runtime", Numeric, "Continuous", Format( "h:m:s", 11, 0 ), Input Format( "h:m:s", 0 ), );
SubNameFirst = RTDT:Sub_name( 1 );
SNFirst = RTDT:Substrate_ID( 1 );
TimeFirst = RTDT:Time( 1 );
EOL = N Rows( RTDT );
 
For( i = 1, i <= EOL, i++,
	If( SubNameFirst != RTDT:Sub_name( i ) || SNFirst != RTDT:Substrate_ID( i ),
		Formula( RTDT:Runtime( i - 1 ) = (RTDT:Time( i - 1 ) - TimeFirst) );
		SubNameFirst = RTDT:Sub_name( i );
		SNFirst = RTDT:Substrate_ID( i );
		TimeFirst = RTDT:Time( i );
	)
);

All I get for results are ".". Is my issue with format of the time column? I am thinking the if() statement never occurs because I get no results or errors.

Any help would be appreciated. Thanks

Data table is attached.

Re: Calculate time difference

txnelson — Fri, 13 Apr 2018 17:05:00 GMT

You appear to have a syntax issue. You are using () for subscripting, where you should be using []. Also, you seem.

I think the only formula you need for your column called Runtime is:

Col Max( :Time, :Sub_name, :Substrate_ID )
-Col Min( :Time, :Sub_name, :Substrate_ID )

RTDT << New Column( "Runtime",
	Numeric,
	"Continuous",
	Format( "h:m:s", 11, 0 ),
	Input Format( "h:m:s", 0 ),
	formula( Col Max( :Time, :Sub_name, :Substrate_ID ) - Col Min( :Time, :Sub_name, :Substrate_ID ) )
);

Re: Calculate time difference

KST-CPT — Fri, 13 Apr 2018 18:15:55 GMT

You are a true phenom. Not being a software engineer, I spent a couple hours trying to do what I did and you solved it with a single equation. Amazing. Thank you.