Conditional formatting of cells in a datable with If-then operating on multiple datasets column cells

[#1]

Hello experts, 

I will try my best to explain the problem I am having. 

1. I have 2 data sets: one containing the median values of lot-A in different columns and one containing std dev of some parameters. 

Median data table

Std dev data table:

2. I want to compare the risk_lot column value row value(5) to [baseline(4)  +  2*std dev datatable:Lot(2)] and [baseline(4)  -  2*std dev datatable:Lot(2)]. If it falls between this range i.e. 0<5<8 for this example, then I want to color risk_lot cell to green and if it falls outside the window then red. 

3. I tried with the code below but its giving me "Cannot convert argument to number error" that makes me think that somehow my syntax is messed up. 

4. Looked at couple of examples in the forum as well but does not work. 

5. I have jmp12. 

Please help. Thanks.

dt_summary<<select where(:Statistics=="Std Dev");
dt_std_subset=subset(output table("Std summary"),selected rows(1),selected columns(0));

dt_summary<<Bring Window to Front;
dt_summary<<invert row selection;

dt_summary<<select where(:Statistics=="Median");
dt_med_subset=subset(output table("Median summary"),selected rows(1),selected columns(0));

current data table(dt_med_subset);
ncol=N cols(current data table());

For Each Row(
	For(i=4,i<ncol+1,i++,
		If ((column(i)>formula(column(3)+2*column(dt_std_subset,3)))|(column(i)<formula(column(3)-2*column(dt_std_subset,3))),
		Column(i)<<color cells ("red",Row()),Column(i)<<color cells("green",Row());
		);
	);
);

[#2]

1. Could you show what the dt_summary data table looks like.  The code you show that is creating the Median and STD data tables does not appear to be complete in it's ability to create the 2 tables as you show?
2. Are the Median and STD data tables complete in the number of columns they will have?
3. Is there a reason you are not just joining the Median and STD data table into a single table, making the conditional cell coloring a more straight forward process?

[#3]

Hello Jim, 

Thanks for helping me out with this. So this is what the summary table looks like. I had tried your previous solution in one of the post but wasn't able to get it working so I thought to subset this table into std dev and median table. I thought it might be easier to modify the cells in the median subset table by referencing column values from std dev table and doing some simple math with it but got stuck  

If there is a simpler way then please let me know. Ultimate goal is to just have a median table (subset of summary table) with the cell values color coded based on if-then logic.

Thanks again. 

[#4]

Here is my version of your code.  I separate out the Median and Std Dev data, but then I join the data back together using  Update.

Names default to here(1);
dt_summary = New Table( "summary",
	Add Rows( 4 ),
	Set Header Height( 50 ),
	New Column( "Analysis Columns",
		Character,
		"Nominal",
		Set Values( {"Test-1", "Test-1", "Test-2", "Test-2"} )
	),
	New Column( "Statistics",
		Character,
		"Nominal",
		Set Values( {"Median", "Std Dev", "Median", "Std Dev"} )
	),
	New Column( "baseline",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 2, 3, 4] )
	),
	New Column( "risk_lot",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [4, 7, 19, 9] )
	)
);

dt_summary << select where( :Statistics == "Std Dev" );
dt_std_subset = dt_summary << subset(
	output table( "Std summary" ),
	selected rows( 1 ),
	selected columns( 0 )
);

dt_summary<<invert row selection;

dt_med_subset = dt_summary << subset(
	output table( "Median summary" ),
	selected rows( 1 ),
	selected columns( 0 )
);

dt_std_subset:baseline << set name("Lot");
dt_std_subset << delete columns({"risk_lot"});

Current Data Table( dt_med_subset );
ncol = N Cols( dt_med_subset );

dt_med_subset << Update(
	With( dt_std_subset ),
	Match Columns( :Analysis Columns = :Analysis Columns )
);

close( dt_std_subset, nosave );

current data table( dt_med_subset );

for each row(
	If( :baseline - 2 * :Lot < :risk_lot < :baseline + 2 * :Lot,
		:risk_lot << color cells( "Green", Row()),
		:risk_lot << color cells( "Red", Row())
	)
);

[#5]

Thank for the code. I see that your code references baseline, lot and risk lot columns by their names. I had tried to use nCOL because the original dataset that I have has 40 columns like risk lot and I want to avoid referencing each of them by name since they have super long names. 

How can I use a for loop on all those columns and color code the entire 40 column cells with the same logic as in the last 3 lines of your code? I guess what I am asking is how to color code the entire 40 columns without using explicit column names and use handles/references to those columns instead. Please let me know if there is a work around to do that?

Thank you

[#6 · Accepted solution]

I must admit that I am confused, so I will go back to your original entry and start over.  Here is a conversion of your cell coloring code.  It has 3 changes.

1. I changed the nCol+1 to N Cols(dt_med_subset).  Your code will fail with the original calculation, because when the value of "i" gets to nCol+1, your code will look for a column beyond the end of the data table, and will not be able to find it.
2. I added absolute references to both the std and the med data tables
3. I changed the For Each Row to a normal For() loop.  The code will run faster, and it provides a more precise reference

For( rowNum = 1, rowNum <= N Rows( dt_med_subset ), rowNum++,
	For( i = 4, i < N Cols( dt_med_subset, i++,
			If(
				(Column( dt_med_subset, i )[rowNum] > Column( dt_med_subset, 3 )[rowNum] + 2
				 * Column( dt_std_subset, 3 )[rowNum]) | (Column( dt_med_subset, i )[rowNum]
				 < (Column( dt_med_subset, 3 )[rowNum] - 2 * Column( dt_std_subset, 3 )[rowNum]))
				,
				Column( dt_med_subset, i ) << color cells( "red", rowNum ),
				Column( i ) << color cells( "green", rowNum )
			)
		)
	)
);

[#7]

Thank you Jim. I apologize for not making my ask very clear in the beginning. I guess this is the error I was getting when I was attempting the for-loop. This is why I thought that there might be a special way to point to different dataset columns using their handles but did not think about what will happen toward the end of loop. I will try this out today and will let you know if I encounter any issues. 

[#8]

Thank you Jim. This is working now. However, now I have run into another issue. I want this color coded data table to be a part of a report/final journal along with other graphs below this table. Something like below. When I try to use data table box to add this table to the report, the color coding disappears and there are no border/grid lines. Can you please help me with code to achieve the following layout?

[ Final Analysis Window]

   [Color coded summary table with grid lines.]

   (heading= column 1)   

    [Box plot of column 1 in summary table][Normal quantile plot of column 1 in summary table]

 (heading= column 2)   

    [Box plot of column 2 in summary table][Normal quantile plot of column 2 in summary table]

..

.. and so on. 

Thanks

[#9]

The only way that I have been able to get a copy of a data table that has color in the cells to be moved into a journal, is to first to put the data table into a window by itself, and then to append the temporary window with the data table in it, to where ever it needs to be in the final journal.  See the example script below

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/big class.jmp" );

:age << color cells( "red", [2, 4, 6, 8] );

temp = New Window( "temp", <<journal );
dt << journal;

temp[Table Box( 1 )] << set row borders( 1 ) << set column borders( 1 );

nw = New Window( "Final",
	bb = Border Box( sides( 15 ), top( 5 ), bottom( 5 ), Left( 5 ),
		Right( 5 )
	)
);
bb << append( temp );
temp << close window;