54980_en-US

liliconcom

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

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.

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