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