Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
sperley
Level I

Unable to colour cells in multiple columns via list

I'm trying to format multiple columns to change colour by cell value by using a list of specific column names, but my code only formats the first column in the list.

How do I get the rest of the columns in the list to format as well?

 

Names Default to Here( 1 );

Test_DT = New Table( "Test",
	New Column( "No Color", set values( {2,2,2,2,2,2} ) ),
	New Column( "1-1", set values( {0,1,0,0} ) ),
	New Column( "2-1", set values( {0,0,0,1} ) ),
	New Column( "3-1", set values( {0,0,0,0,4} ) )
);

Col_List = Test_DT << Get Column Names( string );

For( i = Nitems( Col_List ), i > 0, i--,
	If( !Contains( Col_List[i], "-"),
		Remove from( Col_List, i )
	)
);

For( i = 1, i <= N Row( Test_DT ), i++,
	If( Column( Col_List )[i] >= 1,
		Column( Col_List ) << Color Cells( light orange, {i} )
	)
);

sperley_2-1582306028276.png

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Unable to colour cells in multiple columns via list

You did something that I have done too many times.  You are attempting to reference a cell value, but you are not indicating which row.  Take a look at the code below, which Is working.  Also, you were comparing if I was gt 1, and for the results you want, it should be >=.

Names Default To Here( 1 );

Test_DT = New Table( "Test",
	New Column( "No Color", set values( {2, 2, 2, 2, 2, 2} ) ),
	New Column( "1-1", set values( {0, 1, 0, 0} ) ),
	New Column( "2-1", set values( {0, 0, 0, 1} ) ),
	New Column( "3-1", set values( {0, 0, 0, 0, 4} ) )
);

Col_List = Test_DT << Get Column Names( string );

For( i = N Items( Col_List ), i > 0, i--,
	If( !Contains( Col_List[i], "-" ),
		Remove From( Col_List, i )
	)
);
For( ColNum = 1, ColNum <= N Items( Col_List ), ColNum++,
	For( i = 1, i <= N Row( Test_DT ), i++,
		If( (Column( Col_List[colNum] )[i]) >= 1,
			As Column( Col_List[ColNum] ) << Color Cells( light orange, {i} )
		);
	)
);
Jim

View solution in original post

4 REPLIES 4
Highlighted
pmroz
Super User

Re: Unable to colour cells in multiple columns via list

You have to loop over col_list also.

Names Default to Here( 1 );
Test_DT = New Table( "Test",
	New Column( "No Color", set values( {2,2,2,2,2,2} ) ),
	New Column( "1-1", set values( {0,1,0,0} ) ),
	New Column( "2-1", set values( {0,0,0,1} ) ),
	New Column( "3-1", set values( {0,0,0,0,4} ) )
);
Col_List = Test_DT << Get Column Names( string );
For( i = Nitems( Col_List ), i > 0, i--,
	If( !Contains( Col_List[i], "-"),
		Remove from( Col_List, i )
	)
);
For( i = 1, i <= N Row( Test_DT ), i++,
	for (k = 1, k <= nitems(col_list), k++,
		If ( Column( Col_List[k] )[i] >= 1,
			Column( Col_List[k] ) << Color Cells( light orange, {i} )
		);
	);
);
Highlighted
txnelson
Super User

Re: Unable to colour cells in multiple columns via list

You did something that I have done too many times.  You are attempting to reference a cell value, but you are not indicating which row.  Take a look at the code below, which Is working.  Also, you were comparing if I was gt 1, and for the results you want, it should be >=.

Names Default To Here( 1 );

Test_DT = New Table( "Test",
	New Column( "No Color", set values( {2, 2, 2, 2, 2, 2} ) ),
	New Column( "1-1", set values( {0, 1, 0, 0} ) ),
	New Column( "2-1", set values( {0, 0, 0, 1} ) ),
	New Column( "3-1", set values( {0, 0, 0, 0, 4} ) )
);

Col_List = Test_DT << Get Column Names( string );

For( i = N Items( Col_List ), i > 0, i--,
	If( !Contains( Col_List[i], "-" ),
		Remove From( Col_List, i )
	)
);
For( ColNum = 1, ColNum <= N Items( Col_List ), ColNum++,
	For( i = 1, i <= N Row( Test_DT ), i++,
		If( (Column( Col_List[colNum] )[i]) >= 1,
			As Column( Col_List[ColNum] ) << Color Cells( light orange, {i} )
		);
	)
);
Jim

View solution in original post

Highlighted
pmroz
Super User

Re: Unable to colour cells in multiple columns via list

Couldn't resist a slight improvement.  Collects all rows into a matrix for a column and sets colors all at once.  More efficient than one row at a time.  Had to use as column in the get rows where clause.

Names Default to Here( 1 );

Test_DT = New Table( "Test",
	New Column( "No Color", set values( {2,2,2,2,2,2} ) ),
	New Column( "1-1", set values( {0,1,0,0} ) ),
	New Column( "2-1", set values( {0,0,0,1} ) ),
	New Column( "3-1", set values( {0,0,0,0,4} ) )
);

Col_List = Test_DT << Get Column Names( string );

For( i = Nitems( Col_List ), i > 0, i--,
	If( !Contains( Col_List[i], "-"),
		Remove from( Col_List, i )
	)
);

for (k = 1, k <= nitems(col_list), k++,
	one_col = Col_List[k];
	orange_rows = test_dt << get rows where(as column(test_dt, one_col) >= 1);
	if (nrows(orange_rows) > 0,
		Column(test_dt, one_col) << Color Cells( light orange, orange_rows );
	);
);
Highlighted
sperley
Level I

Re: Unable to colour cells in multiple columns via list

That worked wonderfully. Thank you!
Article Labels

    There are no labels assigned to this post.