cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
robust1972
Level IV

can we color the cells in jmp table as we do in excel? I mean really give colors to the part of table/cells interested

can we color the cells in jmp table as we do in excel? I mean really give colors to the part of table/cells interested?

I tried below jsl but it only works when you plot the data in graphs but not in the table itself?

 

Thanks in advance!

Evan

 

 

dt=Data Table( "Bin Wafer Map" );
For(i=4, i<=N col(dt),i++,
dt:i << Set Property(
"Value Colors",
{1=Green,3 = Red, 4 = Yellow,5=orange, 6 = Magenta, 7 =Blue,8 = Cyan,9=BlueCyan}
));

 

1 ACCEPTED SOLUTION

Accepted Solutions
Jeff_Perkinson
Community Manager Community Manager

Re: can we color the cells in jmp table as we do in excel? I mean really give colors to the part of table/cells interested

In JMP 9 and beyond try right clicking on a cell in a data table and you'll get a menu that will let you color the cell.

 

6176_JMPScreenSnapz001.png

 

In JSL you can use the Color Cell() message:

 

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

column(dt, “Age”)  <<  Color Cells( {5, {15}} );

 

or the Color Cell by Value() message:

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

:Age <<

Set Property(

  "Value Colors",

  {12 = -13977430, 13 = -3780930, 14 = -

  4157407, 15 = -13596965, 16 = -2210961, 17

   = -10562523}

);

Wait( 1 );

:Age << Color Cell by Value( 1 );
-Jeff

View solution in original post

12 REPLIES 12
Jeff_Perkinson
Community Manager Community Manager

Re: can we color the cells in jmp table as we do in excel? I mean really give colors to the part of table/cells interested

In JMP 9 and beyond try right clicking on a cell in a data table and you'll get a menu that will let you color the cell.

 

6176_JMPScreenSnapz001.png

 

In JSL you can use the Color Cell() message:

 

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

column(dt, “Age”)  <<  Color Cells( {5, {15}} );

 

or the Color Cell by Value() message:

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

:Age <<

Set Property(

  "Value Colors",

  {12 = -13977430, 13 = -3780930, 14 = -

  4157407, 15 = -13596965, 16 = -2210961, 17

   = -10562523}

);

Wait( 1 );

:Age << Color Cell by Value( 1 );
-Jeff
pmroz
Super User

Re: can we color the cells in jmp table as we do in excel? I mean really give colors to the part of table/cells interested

You can apply a value gradient using JSL in the following manner.  Note that to use dynamically calculated values of min, max and average I had to build a string and execute it with eval(parse()).

 

 

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dtcol = column(dt, "weight");

one_min = col min(dtcol);
one_max = col max(dtcol);
one_avg = col mean(dtcol);

gradient_expr = evalinsert("\[
        dtcol << Set Property( "Color Gradient",
                    {"Jet", Range(^one_min^, ^one_max^, ^one_avg^)});
            dtcol << color cell by value(1);
]\");

eval(parse(gradient_expr));

 

SamH
Level III

Re: can we color the cells in jmp table as we do in excel? I mean really give colors to the part of

Hi is there a way to color selected cells? I have a hew hundred cells that are selected and I would like to give a color to stand out.

Thanks

Sam

pmroz
Super User

Re: can we color the cells in jmp table as we do in excel? I mean really give colors to the part of

For a JMP table the selection color is controlled by the Windows selection color.  Here's how to do it under windows 7: https://answers.microsoft.com/en-us/windows/forum/windows_7-desktop/how-to-change-windows-7-highligh...

 

robust1972
Level IV

Re: can we color the cells in jmp table as we do in excel? I mean really give colors to the part of table/cells interested

thanks Jeff and PMroz.

I got the hint and was able to get my wafer map colored by Bin(value). I did not know the color by cell value is off by default. Have not figure out how to tell jmp do nothing if the cell value is empty.

 

 

=Data Table( "Bin Wafer Map" );
For(i=4, i<=N col(dt),i++,
:i << Set Property(
"Value Colors",
1=Green,3 = Red, 4 = Yellow,5=orange, 6 = Magenta, 7 =Blue
,8 = Cyan,9=BlueCyan,.=white}
<<color cell by value(1));

 

 

 

 

6187_Capture.PNG

robust1972
Level IV

Re: can we color the cells in jmp table as we do in excel? I mean really give colors to the part of table/cells interested

finally I got it to color only non-empty cells with desired colors. but there is still an error though the script is executable.

 

 

=Data Table( "Bin Wafer Map" );
(i=4, i<=N col(dt),i++,
(j=1, j<=N Row(dt),j++,
if(dt:i[j]==1,dt:i << Color Cells(Green,{j}),
     if(dt:i[j]==3,dt:i << Color Cells(Red,{j}),
          if(dt:i[j]==4,dt:i << Color Cells(Yellow,{j}),
               if(dt:i[j]==5,dt:i << Color Cells(Orange,{j}),
                    if(dt:i[j]==6,dt:i << Color Cells(Magenta,{j}),
                         if(dt:i[j]==7,dt:i << Color Cells(Blue,{j}),
                              if(dt:i[j]==8,dt:i << Color Cells(Cyan,{j}),
                                   if(dt:i[j]==9,dt:i << Color Cells(BlueCyan,{j}),
))))));


(i=4, i<=N col(dt),i++,
(j=1, j<=N Row(dt),j++,
(!zero or missing(dt:i[j]),dt:i[j]<<color cell by value(1),
)));

 

6195_Capture.PNG

pmroz
Super User

Re: can we color the cells in jmp table as we do in excel? I mean really give colors to the part of table/cells interested

This code is a bit simpler.  Note that I'm using k for a looping variable instead of j; j is a function in JSL.

 

 

color_list = {"Green", "Red", "Yellow", "Purple", "Orange", "Magenta", "Blue", "Cyan", "BlueCyan"};

dt = Data Table( "Bin Wafer Map" );
For( i = 4, i <= N Col( dt ), i++,
    For( k = 1, k <= N Row( dt ), k++,
        one_value = dt:i[k];
        if (! is missing(one_value),
            dt:i << color cells(color_list[one_value], {k});
            dt:i << color cell by value( 1 );
        );
    );
);

 

robust1972
Level IV

Re: can we color the cells in jmp table as we do in excel? I mean really give colors to the part of table/cells interested

thanks, P!

your script is much simpler but it is not able to exclude the empty cells(showing X)

my script trying to send color cell by value to a cell, which works but giving below error. Do you know what the error tells us and how to debug it?

PS:  I tried use below script to add a empty row btw each wafer but it might calls a infinite loop:(, never worked.

 

 

For(a=0, a<(N Row(dt))/11,a++,
dt<<add rows(1,(a+(a+1)*11));
);

 

 

 

Thanks a gain.

6200_Untitled.png

pmroz
Super User

Re: can we color the cells in jmp table as we do in excel? I mean really give colors to the part of table/cells interested

My code excludes the empty cells with the ! is missing() check.  I only get the X's for empty cells when I use a Value Gradient.

Your code is failing because that method expects a column, not a cell.  Again I strongly recommend that you change j to k or something because j is a function in JSL.

I realized that the color cell by value method only needs to be called once per column, so I took it out of the k loop.

color_list = {"Green", "Red", "Yellow", "Purple", "Orange", "Magenta", "Blue", "Cyan", "BlueCyan"};

dt = Data Table( "Bin Wafer Map" );

For( i = 4, i <= N Col( dt ), i++,

    dt:i << color cell by value( 1 );

    For( k =1, k <=N Row( dt ), k++,

        one_value = dt:i[k];

        if (! is missing(one_value),

            dt:i << color cells(color_list[one_value], {k});

        );

    );

);