248665_en-US

liliconcom

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

 

 

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

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

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

Re: Unable to colour cells in multiple columns via listThat worked wonderfully. Thank you!