- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
color a cell by formula
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 Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: color a cell by formula
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);
);
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: color a cell by formula
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: color a cell by formula
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: color a cell by formula
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);
);
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: color a cell by formula
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: color a cell by formula
Thank you so much for the solution.