nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

color a cell by formula

CumulativeMean2

i have a data table in wide format. it has the spec limit. it works fine by doing quality & process -> manage limit -> color out of spec value. The fail value are color using this method. i would like to view it in tall format. See below transposed table.
The data table are transposed then update with limit (column name with LSL and USL). Label is the parameters, 1 to 20 are for different parts.

i would like to color a cell or the value if the reading of the part is < LSL or reading is > USL. can i do it with cols -> formula or does it need JSL?

this is JMP 17.

Accepted answers

jthi

Not the simplest way of doing this, but might work for you. Note that you might have to add extra checks if you have one-sided specification limits

Names Default To Here(1);

dt = Open("$DOWNLOADS/Transpose of u4_trimmed.jmp");

cols = dt << Get Column Names("String");
labelidx = Contains(cols, "Label");
partcols = Remove From(cols, labelidx + 1, Contains(cols, "LSL") - labelidx - 1);

m = J(N Rows(dt), N Items(partcols), 0);

For Each Row(dt,
	m[Row(), 0] = dt[Row(), partcols] < :LSL | dt[Row(), partcols] > :USL
);

For Each({partcol, idx}, partcols,
	color_rows = Loc(m[0, idx]);
	If(N Items(color_rows) > 0,
		Column(dt, partcol) << Color Cells("Red", color_rows);
	);
);

All comments

jthi

As you are performing the checks on row level and colors are generally set on column level, I think you will need to utilize JSL. The JSL for that isn't too difficult and if you can provide example data, JMP Community can most likely help you build it.

One way to build the script would be:

Get measurement columns into a list/associative array (continuous columns between Label / LSL or columns with just numbers in the name)
Use For Each Row to loop over the table
Compare values on the row to the limits while updating the object created in step one with the rows which should be colored for each column
After you have finished looping of the rows, which to looping over the object created in step 1 and set the colors using << Color Cells

CumulativeMean2

here is example data.

column "label" is the parameter. each label has unique LSL and USL.

1 to 20 in column header are different parts. every part has a unique value for each parameter.

for example, if row 3, column "1" with reading of "0.37209999561" is out of LSL & USL of (0.070000000298, 1), then the "0.37209999561" font color changed to red.

Transpose of u4_trimmed.jmp

jthi

Not the simplest way of doing this, but might work for you. Note that you might have to add extra checks if you have one-sided specification limits

Names Default To Here(1);

dt = Open("$DOWNLOADS/Transpose of u4_trimmed.jmp");

cols = dt << Get Column Names("String");
labelidx = Contains(cols, "Label");
partcols = Remove From(cols, labelidx + 1, Contains(cols, "LSL") - labelidx - 1);

m = J(N Rows(dt), N Items(partcols), 0);

For Each Row(dt,
	m[Row(), 0] = dt[Row(), partcols] < :LSL | dt[Row(), partcols] > :USL
);

For Each({partcol, idx}, partcols,
	color_rows = Loc(m[0, idx]);
	If(N Items(color_rows) > 0,
		Column(dt, partcol) << Color Cells("Red", color_rows);
	);
);

CumulativeMean2

it works. sorry for late response. i was trying to figure it out the script, but it seems like this is going to take more time.

SophieGray

Thank you so much for the solution.