I am trying to reset columns with a character ("<") to numeric and retain the "<" in another column

[#1]

Hi - I have a table with columns that are made up of lab results (numeric values) that were entered as a text string.  They are text string because some have a "<" in front of the numeric value.  I need to be able to perform numeric calculations on the values so I am trying to remove the "<" symbol and place it in a new column that is named "ORIGCOLNAME_QUALIFIER" (where ORIGCOLNAME is the name of the column from which the "<" symbols were removed).  

Unfortunately, the below code is not working.

Names Default To Here( 1 );
dt = Current Data Table();

compList = {};
For( i = 1, i <= N Cols( dt ), i++,
colName = Column( dt, i ) << get name;
);

// Create the columns
For Each( {comp, i}, compList,
Eval(
Parse(
"dt << New Column( \!"" || compList[i] || "_QUALIFIER" ||
"\!",
character, nominal,
formula(
x=.; 

if (comp << contains ("<"), x = <);

I get the following message:  

Any input would be most appreciated.

[#2]

Table attached, just in case.

[#3]

This might give some ideas on what you could do

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(2),
	Compress File When Saved(1),
	New Column("Column 1", Character, "Nominal", Set Values({"<1", "<3"})),
	New Column("Column 2", Character, "Nominal", Set Values({"<2", "<4"}))
);


col_names = dt << Get Column Names("String");

For Each({col_name}, col_names,
	dt << New Column(col_name ||"_Q", Numeric, Continuous, << Set Each Value(
		Num(Substitute(AsColumn(dt, col_name), "<", ""));
	));
);

Search Scripting Index and Scripting Guide for Substitute() to learn more about how it can be used

[#4]

Thanks jthi - The first part of your code creates a new 2-row table with pre-named columns.  

Names Default To Here( 1 );
dt = New Table( "Untitled",
	Add Rows( 2 ),
	Compress File When Saved( 1 ),
	New Column( "Column 1", Character, "Nominal", Set Values( {"<1", "<3"} ) ),
	New Column( "Column 2", Character, "Nominal", Set Values( {"<2", "<4"} ) )
);

However, I am trying to keep my column names intact while adding a new column with name corresponding to each of my existing columns but with "_qualifier" on the end.    (Ex:  say my existing columns are chloride (character), sulfate (sulfate), ..., the new columns would be chloride (numeric), sulfate (numeric), chloride_qualifier (character), sulfate_qualifier (character), etc. 

[#5]

Just substitute that part with a reference to your table. You might also want to cleanup the column selection if you know which should be used (instead of just using << get column names("String") to get all columns).

[#6]

Unfortunately I'm not following how that code applies to my original question.  I probably was not clear in my original post or am missing something (I'm a jsl novice).  In any case, I'll keep trying and may need to re-frame my question.  (By the way, I have a very large number of columns in play, and all but the datetime column need this treatment - far too many to define by hand.)  Thanks again. 

[#7 · Accepted solution]

First, when posting JSL to a Community Discussion page, please use the JSL icon at the top of the page, so the code properly shows up ion the page.

Below is a script that checks for < and for any Post Script values such as "J" or "UJ" etc, and moves the values to a new Qualifier column.  It then converts the column from a character column to a numeric column

Names Default To Here( 1 );
dt = Current Data Table();

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

For Each( {col, i}, colNames,
	GTFound = "NO";
	postScriptFound = "NO";
	
	// Find rows that have < in them
	theRows = dt << get rows where( Contains( As Column( dt, col ), "<" ) );
	// If < is found then 
	//      1. Create a new column to hold the < in
	//      2. Loop through the rows with < and
	//         1. Add < to the appropriate row in the new Qualifier column
	//         2. Delete the < from the current column
	If( N Rows( theRows ) > 0,
		GTFound = "YES";
		dt << add Multiple Columns( col, 1, Character );
		Column( dt, N Cols( dt ) ) << set name( col || "_QUALIFIER" );
		dt << move selected column( after( As Column( col ) ) );
		For Each( {fixRow, k}, theRows,
			As Column( dt, col || "_QUALIFIER" )[fixRow] = "<";
			As Column( dt, col )[fixRow] = Substr( Column( col )[fixRow], 2 );
		);
	);
	
	// Find if there are any post script values on any of the rows
	theRows = dt << get rows where( Word( 2, As Column( dt, col ), " " ) != "" );
	
	// If found, then if a new column has not already been add, add the new Qualifer column
	If( N Rows( theRows ) > 0,
		postScriptFound = "YES";
		If( GTFound == "NO",
			dt << add Multiple Columns( col, 1, Character );
			Column( dt, N Cols( dt ) ) << set name( col || "_QUALIFIER" );
			dt << move selected column( after( As Column( col ) ) );
		);

		// Loop through the rows with post scripts and
		//     1. Add the post scripts to the Qualifer column
		//     2. Delete the post scripts from the current column
		For Each( {fixRow, k}, theRows,
			As Column( dt, col || "_QUALIFIER" )[fixRow] = Trim(
				As Column( dt, col || "_QUALIFIER" )[fixRow] || " " || Word( 2, As Column( dt, col )[fixRow], " " )
			);
			As Column( dt, col )[fixRow] = Word( 1, As Column( dt, col )[fixRow], " " );
		);
	);
	
	// If eiter < or Post Scripts were found, convert the column to numeric, continuous
	If( GTFound == "YES" | postScriptFound == "YES",
		Column( dt, col ) << data type( numeric ) << modeling type( continuous )
	);
);

[#8]

That worked perfectly.  And the comment lines are most helpful as they will help me understand each step in the process.  This augments what I can find in the help documentation as I learn how to actually apply these jsl fundamentals to my dataset.  Thank you very much.

[#9]

I might not understand your need, and there are likely more efficient ways to do this, especially if your data has many rows, but here is a brute force method if this table looks like the result you want.

Names Default To Here(1);
id_colnames = {"well", "Sample Date"};

dt = Current Data Table();
nr =nrow(dt);

col_names = dt << Get Column Names("String");

For Each({col_name}, col_names,
   If( !Contains(id_colnames, col_name),
   	  dt << New Column(col_name||"_Q", Character, << Set Each Value(
   	  	If(Contains(AsColumn(dt,col_name), "<"), "<",""))
   	  );
   	  
      curr_col = column(dt,col_name);
      For(i=1, i<=nr, i++,
         curr_col[i]= Substitute( word(1, curr_col[i]) ,"<","");
       );
      curr_col << Set Data Type(Numeric) << Set Modeling Type ("Continuous");
   	); // end If()
 );  //end For Each()
 

[#10]

Thanks GZMorngan0!    Sorry I'm slow responding.  I just checked back on the thread. 

I appreciate seeing another technique.