topic Re: Problem with Lookup table / Loc sorted in Discussions

Problem with Lookup table / Loc sorted

MrAB — Sat, 10 Jun 2023 23:41:15 GMT

I am trying to find the time at which the measured value is half in this data table. My desired output would be a summary table with Time at half measurement by sample.

This is the summery table generated by the script and as you can see the time at half measure is not correct.

I have made the following script that uses Loc sorted to look up the values from this table but i'm having difficulty getting it to work. Are there any errors in this script? Is this the right method for what I'm looking to do and if not does anyone have any suggestions on how I can do this? one thing to note is the half value might not match a time exactly so I will be looking for the time either before of after the half measure point.

Names Default To Here(1);

Data Table( "Data" ) << Summary(
	Group( :Sample, ),
	Max( :Measurement ),
	Freq( "None" ),
	Weight( "None" )
);

New Column( "Half Max Measure",	Formula	(:Name("Max(Measurement)") / 2));

New Column( "Time At Half Measurement",	Format ("h:m"), Formula(
dt = Data Table( "Data" );
If(dt:Sample == Sample,
Bracket = Loc Sorted( dt:Measurement << getvalues, :Half Max Measure );
dt:Time[Bracket])));

Re: Problem with Lookup table / Loc sorted

peng_liu — Fri, 03 Dec 2021 13:31:52 GMT

"Loc Sorted" requires the first argument sorted to work properly.

On the other hand, the following might give what you want, assuming the middle exist among data.

data table("Data") << new column("is match", 
	formula(:Measurement == Col Max( :Measurement, :Sample ) / 2)
)

If the middle may not exist among data, check whether a sample falls within a range which you can define.

Re: Problem with Lookup table / Loc sorted

txnelson — Fri, 03 Dec 2021 15:15:29 GMT

Here is my take on solving the problem. It handles the cases where either the Max Measurement is an even number, and therefore an exact row to get the Half Time from exists, or if the Max Measurement is an odd number, it will interpolate the Time value to come up with the Half Time value

Names Default To Here( 1 );

dt = Data Table( "Data" );

dtSum = dt << Summary(
	Group( :Sample, ),
	Max( :Measurement ),
	Freq( "None" ),
	Weight( "None" ),
	Link to Original Data Table( 0 ),
	Output Table( "The Summaries" )
);

dtSum << New Column( "Half Max Measure", Set Each Value( :Name( "Max(Measurement)" ) / 2 ) );
dtSum << New Column( "Time At Half Measurement", Format( "h:m:s" ) );

// Get the Half Max Measure
For( i = 1, i <= N Rows( dtSum ), i++,
	theRow = dt << get rows where(
		dt:Sample == dtsum:Sample[i] & dt:Measurement == dtSum:Half Max Measure[i]
	);
	If( N Rows( theRow ) > 0,
		dtSum:Time At Half Measurement[i] = dt:Time[theRow[1]]
	, 
		// No Measurement was taken at exact Half Time, so interprolate the finding
		rowBefore = Max(
			dt << get rows where(
				dt:Sample == dtsum:Sample[i] & dt:Measurement > dtSum:Half Max Measure[i]
			)
		);
		rowAfter = Min(
			dt << get rows where(
				dt:Sample == dtsum:Sample[i] & dt:Measurement < dtSum:Half Max Measure[i]
			)
		);
		ratio = (dt:Measurement[rowBefore] - dtSum:Half Max Measure[i]) / (dt:Measurement[
		rowBefore] - dt:Measurement[rowAfter]);
		dtSum:Time At Half Measurement[i] = dt:Time[rowBefore] + ratio * (dt:Time[rowAfter] -
		dt:Time[rowBefore]);
	);
);