topic Compare values in each row to values in first row (limits) in Discussions

Compare values in each row to values in first row (limits)

EndersGame — Mon, 18 Nov 2024 17:01:55 GMT

Hi, I have a row of limits that I would like the rest of my rows to be compared to and highlight cells that are above that limit. How do I do that?

Or if someone could show me how to select the first row, that would also help.

This is what I am trying:

dt = Current Data Table();
dt << select where( :"Value_in"n[Row()] > :"Value_in"n[Row()==0]) << Color Cells("Red");

Thanks in advance.

Re: Compare values in each row to values in first row (limits)

txnelson — Mon, 18 Nov 2024 17:26:39 GMT

Welcome to the Community.

Here is an example of how I would approach this

names default to here(1);
dt=New Table( "Example",
	Add Rows( 100 ),
	New Column( "values",
		Formula( Random Integer( 50, 100 ) )
	),
	New Column( "limit",
		Numeric,
		Set Values(
			[90]
		)
	)
);

dt:values << color cells(green,dt<<get rows where(:values > :limit[1]));

Re: Compare values in each row to values in first row (limits)

jthi — Mon, 18 Nov 2024 17:56:59 GMT

You can use data table subscripting to pick value from any row. Also it isn't necessary to select the rows

Names Default To Here(1); 

dt = open("$SAMPLE_DATA/Big Class.jmp");

over_limit = dt << Get Rows Where(:weight > :weight[1]);
:weight << Color Cells("Red", over_limit);

Re: Compare values in each row to values in first row (limits)

EndersGame — Mon, 18 Nov 2024 18:01:59 GMT

Thank you, I tried something slightly different (since I need :values[0] cell to contain the limit value) and it worked. Thanks for responding!

Here's my code, btw, not as elegant though, and I have multiple copies of this for each of my columns I want to compare:

::dt = Current Data Table();
For( ::i = 2, ::i <= N Row( ::dt ), ::i++,
	If(:"colname_(unit)"n[::i] > :"colname_(unit)"n[1],
		:"colname_(unit)"n << Color Cells("Red", ::i);
	);
);

Re: Compare values in each row to values in first row (limits)

txnelson — Mon, 18 Nov 2024 18:39:22 GMT

2 comments on your response and data table layout.

Using a For() loop is very inefficient compared to using a Get Rows Where()
Using a JMP data table like it is an Excel data table is problematic. A JMP data table is designed for analysis. Therefore, it is column based, not cell based as Excel is. A limit such as you have placed in the table forces the user to have to program around the unique rows. The typical way to handle items such as a limit is to have them in a separate data table, or to save them as a Column Property. JMP has a Spec Limit column property that might be exactly what you could use, and if not, then you can make up a new column property and place it in there.