813776_en-US

liliconcom

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

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. 

Basic Data Analysis and ModelingRe: Compare values in each row to values in first row (limits)

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=""> :limit[1]));</GET>

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

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)

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)

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.