Perform Formula on Subset Columns

[#1]

I have created a script to make a subset table of a larger table, but it isn't working correctly. My goal is to have the user select which columns they would like in the subset table, and then have the script perform a formula on the columns before it places the data into the new table. So far my script will pull up the dialogue box for the user interaction, but when I select the columns in the dialogue box, they aren't the ones that show up in the subset table. I'm not sure where my problem lays with that. (Note: the user will be selecting columns with data in them, rather than sample information, so in the example, columns with column names starting with "Column 10...".)

Additionally, I'm not sure where I would insert the formula. Basically the columns the user selects from the dialogue box will have some cells with < symbols, for the new subset table, I would like any cell without a less than symbol to turn to zero and any cell with a < symbol to have the < symbol removed placing only the number beside it in the subset table. So the new subset table with have zeros for detected values and the detection limit for the non detected compounds.

Here's my script so far.

Names Default To Here( 1 );

dt = Data Table("Example Data");

col_dlg = new window("Column Select",
	panelbox("Select columns to create NonDetect table with",
		col_clist = collistbox(all, width(200)),
		buttonbox("Ok",
			selected_column_list = col_clist << getselected;
			dt2 = dt << Subset(Output Table("NonDetect"),columns(col_clist));
			col_dlg << close window;
	)
	)
);

Thank you for any help!

[#2]

Thanks for your quick response! I tried 2 but I end up with zeros from the detected values and blanks for any cells with < symbols. Same result that I get with 1.

[#3]

Maybe I put the ('s in the wrong spot? Here's what I have right now.

Names Default To Here( 1 );

dt = Data Table( "Example Data" );

col_dlg = New Window( "Column Select",
	Panel Box( "Select columns to create NonDetect table with",
		col_clist = Col List Box( all, width( 200 ) ),
		Button Box( "Ok",
			selected_column_list = col_clist << getselected;
			dt2 = dt << Subset( Output Table( "NonDetect" ), columns( selected_column_list ) );
			// Loop across the columns in the new data table and convert them to numeric
			// Cells with nonnumeric values will be set to Missing Values, which then can
			// be identified and set to 0
			For( i = 1, i <= N Items( selected_column_list ), i++,
				// Select the rows where there is a "<" for the target column
				dt2 << Select where(Contains(As Column( dt2, selected_column_list[i] ),"<" )==1);
				// Get rid of the "<" making the value a valid numeric value
				Column( dt2, selected_column_list[i] )[dt2 << get selected rows ()] = 
					substr(As Column( dt2, selected_column_list[i] ),2);
				// Select all of the currently non selected columns and unselect the current ones
				dt2 << invert row selection;
				// Set for the target column all of the currently selected rows to "0"	
				Column( dt2, selected_column_list[i] )[dt2 << get selected rows ()] = "0";
				// Convert the column to a numeric/continuous column
				Column( dt2, selected_column_list[i] ) << data type( numeric ) <<
					modeling type( continuous );
			);
			col_dlg << close window;
		)
	)
);

[#4 · Accepted solution]

It turns out that the Substr() function does not work across the selected rows(not sure why), so I changed up the code a bit and tested it out(I obviously had not tested my last version)

Names Default To Here( 1 );

dt = Data Table( "Example Data" );

col_dlg = New Window( "Column Select",
	Panel Box( "Select columns to create NonDetect table with",
		col_clist = Col List Box( all, width( 200 ) ),
		Button Box( "Ok",
			selected_column_list = col_clist << getselected;
			dt2 = dt << Subset( Output Table( "NonDetect" ), columns( selected_column_list ) );
			// Loop across the columns in the new data table and convert them to numeric
			// Cells with nonnumeric values will be set to Missing Values, which then can
			// be identified and set to 0
			For( i = 1, i <= N Items( selected_column_list ), i++, 
				// Select all of the currently non selected columns and unselect the current ones
				dt2 << select where( Contains( As Column( dt2, selected_column_list[i] ), "<" ) == 0 );
				// Set for the target column all of the currently selected rows to "0"	
				Column( dt2, selected_column_list[i] )[dt2 << get selected rows] = "0";
				// Find the rows with the "<" in them
				dt2 << invert row selection;
				selRows = dt2 << get selected rows;
				//selRows= dt2 << Get Rows Where(Contains(As Column( dt2, selected_column_list[i] ),"<" )==1);
				// Get rid of the "<" making the value a valid numeric value
				For( k = 1, k <= N Rows( selRows ), k++,
					Column( dt2, selected_column_list[i] )[selRows[k]] = Substr( As Column( dt2, selected_column_list[i] )[selRows[k]], 2 )
				);
				// Convert the column to a numeric/continuous column
				Column( dt2, selected_column_list[i] ) << data type( numeric ) << modeling type( continuous );
				
			);
			col_dlg << close window;
		)
	)
);

[#5]

That worked! Thank you so much for your time and patience. I truly appreciate it!

Lacey

[#6]

Now it's your chance to pay the contributors to the Discussion Community........dig into the Scripting Guide, and Scripting Index as become an experienced user and give back by your participation.