topic Replace cell values in Discussions

Replace cell values

Jackie_ — Tue, 07 May 2024 13:42:12 GMT

Hi,

I am trying to replace all the failing cells in the attached data table, which is highlighted in red with 1 and passing cells with 0.

Found this but something doesn't work-

How do you get the color of a specific cell in a data table using JSL?

Is there any better way to replace the cell values?

Here is what I tried

Names Default To Here( 1 );
dt = Current Data Table();
cols = dt << get column group( "Tests" );

get_cell_color = Function( {col, r}, 

	s = col << get script;

     //Locate argument "Color Cells" in column script

	For( i = 1, i <= N Arg( s ), i++, 

		If( Head Name( Arg( s, i ) ) == "Color Cells", 

			p = i;

			Break();

		)

	);

     //Identify the sublist that contains the color of row r

	L = Arg( Arg( s, p ), 1 );

	For( i = 1, N Items( L ), i++, 

		If( N Row( Loc( L[i][2], r ) ) == 1, 

			color = L[i][1];

			Break();

		)

	);

	color;

);

// 


test_cols = Transform Each( {col}, cols, col << get name );
dt << Select All Rows;
selrow = dt << get Selected Rows();
dt << begin data update;

For Each( {testcol, idx}, test_cols, 

	For Each( {ro, idx2}, selrow, 
	
		
		cellcolor = get_cell_color( Column( testcol ), ro );
		If( !cellcolor == 3, 
		
			Column( testcol )[ro] = 0
		
		)

		;
	)
);
dt << end data update;

Thanks,

Re: Replace cell values

jthi — Tue, 07 May 2024 13:49:33 GMT

might provide other suggestion. With small modification to create_failed_column_after (you should also rename it) you can get it to update the values instead of creating new column with the values.

Re: Replace cell values

jthi — Tue, 07 May 2024 15:39:49 GMT

This is one way how you could modify the script found from that post

Names Default To Here(1);

get_colored_cells = Function({dt, colname}, {Default Local},
	colscript = Column(dt, colname) << Get Script;
	l = Substitute(Name Expr(colscript), Expr(New Column()), List());
	colored_cells = {};
	Try(
		colors = l["Color Cells"];
		If(Type(colors[1]) == "List",
			jmptrickery = 1,
			jmptrickery = 0
		);
		For Each({color}, colors,
			If(jmptrickery,
				Insert Into(colored_cells, color[2]),
				Insert Into(colored_cells, color)
			)
		);
	);
	Return(Matrix(colored_cells));
);

colored_to_pass_fail = function({dt, colname}, {Default Local},
	failed_rows = get_colored_cells(dt, colname);
	row_vals = J(1, N Rows(dt), 0);
	row_vals[failed_rows] = 1;

	Column(dt, colname) << set values(row_vals);
	// dt[0, colname] = row_vals`; // or data table subscripting
);

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

wait(1);
dt << Select Rows([4,5,6,7, 10,11, 14, 15]);
:height << Color Cells(3);
dt << clear select;
wait(1);


dt << Begin Data Update;
For Each({colname}, dt << Get Column Names("String", Continuous),
	colored_to_pass_fail(dt, colname);
);
dt << End Data Update;

wait(0);

Re: Replace cell values

Jackie_ — Tue, 07 May 2024 17:06:30 GMT

Thank Jarmo