topic Re: How can I select rows that are the same based on one (first) column but different based on another column in Discussions

How can I select rows that are the same based on one (first) column but different based on another column

robin_preston_d — Wed, 30 Oct 2024 09:08:39 GMT

Hi everyone.

I am trying to formulate a script (JMP 13) that will allow me to isolate records (rows) that are the same based on one column but different based on another column. In more detail, I have made two observations/measurements on the same sample but for some samples the outcome is different. How can I isolate these cases? I have tried this (and I am likely missing something very basic - apologies :(

// Assume the columns are "ColumnA" and "ColumnB"
dt = Current Data Table(); // Reference the current data table

// Loop through each row and compare values in the specified columns
For(i = 1, i <= N Row(dt), i++, 
// Compare with every other row to find matches on ColumnA but differences on ColumnB
	For(j = i + 1, j <= N Row(dt), j++,
		If(dt:ColumnA[i] == dt:ColumnA[j] & dt:ColumnB[i] != dt:ColumnB[j],
			dt << Select Row(i);
			dt << Select Row(j);
		)
	)
);

Assistance with this problem will be greatly appreciate.

Re: How can I select rows that are the same based on one (first) column but different based on another column

txnelson — Wed, 30 Oct 2024 09:48:08 GMT

The function

dt << Select Where()

will select all of the rows that match the specified condition all at once, which will eliminate the second For() loop. It will be far more efficient also. Below is an example

Names Default To Here( 1 );
dt = 
// Open Data Table: Big Class.jmp
// → Data Table( "Big Class" )
Open( "$SAMPLE_DATA/Big Class.jmp" );

For Each Row(
	currHt = :height[Row()];
	currWt = :weight[Row()];

	dt << select where( :height == currHt & :weight != currWt, current selection( "extend" ) );

);

Re: How can I select rows that are the same based on one (first) column but different based on another column

robin_preston_d — Wed, 30 Oct 2024 09:57:39 GMT

Excellent, thanks so much Jim.