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

[#1]

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. 

[#2 · Accepted solution]

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

[#3]

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

[#4]

2 comments on your response and data table layout. 

1. Using a For() loop is very inefficient compared to using a Get Rows Where()
2. 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.

[#5]

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