topic Color Cells With Missing Value and Greater Than Value in Discussions

Color Cells With Missing Value and Greater Than Value

rmiyasato — Wed, 21 Feb 2018 20:28:59 GMT

Hello Forum!

I want to color cells in columns that start with "Diff" that has a value greater than .1 OR has a missing value (.) The number of columns that start with "Diff" will grow as the data set grows. Here's what I have so far but probably won't work in the long run as the # or columns grow.

Names Default to Here(1);

dt=current data table();

dt:Diff T00 << color cells("Red", dt<<get rows where(dt:Diff T00 >= .1));
dt:Diff T01 << color cells("Red", dt<<get rows where(dt:Diff T01 >= .1));
dt:Diff T02 << color cells("Red", dt<<get rows where(dt:Diff T02 >= .1));
dt:Diff T03 << color cells("Red", dt<<get rows where(dt:Diff T03 >= .1));
dt:Diff T04 << color cells("Red", dt<<get rows where(dt:Diff T04 >= .1));
dt:Diff T05 << color cells("Red", dt<<get rows where(dt:Diff T05 >= .1));
dt:Diff T06 << color cells("Red", dt<<get rows where(dt:Diff T06 >= .1));
dt:Diff T07 << color cells("Red", dt<<get rows where(dt:Diff T07 >= .1));
dt:Diff T08 << color cells("Red", dt<<get rows where(dt:Diff T08 >= .1));
dt:Diff T09 << color cells("Red", dt<<get rows where(dt:Diff T09 >= .1));
dt:Diff T10 << color cells("Red", dt<<get rows where(dt:Diff T10 >= .1));

Re: Color Cells With Missing Value and Greater Than Value

txnelson — Wed, 21 Feb 2018 21:05:12 GMT

I think something like this will work

Names Default To Here( 1 );

dt = Current Data Table();

colNames = dt << get column names( numeric, string );

For( i = 1, i <= N Items( colNames ), i++,
	If( Left( colNames[i], 4 ) != "Diff",
		Column( dt, colNames ) << color cells( "Red", dt << get rows where( As Column( dt, colNames ) >= .1 ) )
	)
);

Re: Color Cells With Missing Value and Greater Than Value

rmiyasato — Wed, 21 Feb 2018 22:09:34 GMT

Hmmm... that's odd. The wong column gets highlighted. I've attached the script and csv. I do get an error saying that the column "Data" needs to be a character type. But I need it to be numeric for the calc.

Values in columns starting with Diff*** > .1 ==Red

Values in columns starting with Diff*** = missing values ==Red

Re: Color Cells With Missing Value and Greater Than Value

txnelson — Thu, 22 Feb 2018 03:50:20 GMT

There were 2 errors.....

1. I reversed the logic of the If() function I had specified....I had "!=" and it needs to be "==".

2. The colNames references in the color cells() had to reference the specific column....my error too

I have also cleaned up your code a bit......nothing major,

Names Default To Here( 1 );
dt1 = Open(
	Pick File(),
	columns(
		Column( "IM#", Character, "Nominal" ),
		Column( "LREQ", Numeric, "Continuous", Format( "Best", 10 ) ),
		Column( "DATE/TIME", Character, "Nominal" ),
		Column( "T(x)", Character, "Nominal" ),
		Column( "BOARD", Numeric, "Continuous", Format( "Best", 10 ) ),
		Column( "CHANNEL", Numeric, "Continuous", Format( "Best", 10 ) ),
		Column( "DATA", Numeric, "Continuous", Format( "Best", 10 ) )
	),
	Import Settings(
		End Of Line( CRLF ),
		End Of Field( Tab, Comma, CSV( 0 ) ),
		Strip Quotes( 1 ),
		Use Apostrophe as Quotation Mark( 0 ),
		Scan Whole File( 1 ),
		Treat empty columns as numeric( 0 ),
		CompressNumericColumns( 0 ),
		CompressCharacterColumns( 0 ),
		CompressAllowListCheck( 0 ),
		Labels( 1 ),
		Column Names Start( 1 ),
		Data Starts( 2 ),
		Lines To Read( "All" ),
		Year Rule( "20xx" )
	)
) << Set Name( "DT1" );


Data Table( "DT1" ) << Sort(
	By( :BOARD, :CHANNEL, :Name( "T(x)" ) ),
	Order( Ascending, Ascending, Ascending ),
	replace table
) << New Column( "T00 Value",
	formula( If( :Name( "T(x)" ) == "T00", :Data, :T00 Value[Row() - 1] ) )
) << New Column( "Diff",
	formula( (:Data - :T00 Value) / :T00 Value ),
	Format( "Percent" )
);

dt = Data Table( "DT1" ) << Split(
	Split By( :Name( "T(x)" ) ),
	Split( :DATA, :Name( "Diff" ) ),
	Group( :IM#, :LREQ, :BOARD, :CHANNEL ),
	Remaining Columns( Drop All ),
	Output Table Name( "DT2" )
);

Close( Data Table( "DT1" ), No Save );

//Names Default To Here( 1 );

//dt = Current Data Table();

colNames = dt << get column names( numeric, string );

For( i = 1, i <= N Items( colNames ), i++,
	If( Left( colNames[i], 4 ) == "Diff",
		Column( dt, colNames[i] ) << color cells(
			"Red",
			dt << get rows where( As Column( dt, colNames[i] ) >= .1 )
		)
	)
);