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; } } }

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

anuragg

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());
		);
	);
);

Accepted answers

txnelson

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.

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.
I added absolute references to both the std and the med data tables
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 )
			)
		)
	)
);

All comments

txnelson

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?
Are the Median and STD data tables complete in the number of columns they will have?
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?

anuragg

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.

txnelson

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())
	)
);

anuragg

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