Choose Language Hide Translation Bar
Highlighted
KST-CPT
Level II

Calculate time difference

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Calculate time difference

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

View solution in original post

2 REPLIES 2
Highlighted
txnelson
Super User

Re: Calculate time difference

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

View solution in original post

Highlighted
KST-CPT
Level II

Re: Calculate time difference

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.

Article Labels

    There are no labels assigned to this post.