How to check if a list contents is all numeric without loops

[#1]

Hi,

I am writing some code which imports data and I would like to do some data checking similar to MessyTables for python.

Data can be accidentally imported as character due to "NULL" or similar being present in the data. After removing the common instances of these I would like to check the data again to see if it is numeric.
The easiest way to do this is to use Try() and Matrix() after removing any "" around items in the list.
I could loop through the columnKeys and if the Num() conversion fails trigger a Throw(), this would be wasteful if the table is long and the column has many unique numbers.

The code below writes an error to the long, which is fine. However it also triggers a JMP Alert window once I incorporate it into my add-in but not if I run it directly.

Any suggestions of alternatives to check if a list is numeric or to suppress the JMP Alert dialog?

Thanks,

Stephen

dt = New Table( "Check Data",
Add Rows( 2 ),
New Column( "Column 1", Character, "Nominal", Set Values( {"+1%", "2", "-45"} ) )
);

a = Column(dt, "Column 1" ) << Get Values;

Try(
b = Matrix( Parse( Substitute( Char( a ), "\!"", "", " " ) ) );
Show( "Converted to Numeric." );
Show(b);
,
Show( "Not all Numeric..." );
Show(a);
);

[#2]

I find the easiest way to check to see if an imported column should actually be numeric, when in fact it was imported as character is to use this simple line of code. It returns the number of numeric values found:

numberofnumerics = Col Number(num(<the target column>));

you can also get a ratio of the number of numerics to the number of non blank columns

ratio = Col Number(num(<the target column>)) / Col Number(<the target column>);

[#3]

Thank you! I've been looking for sth like this for hours.

[#4]

This seems pretty fast. It just uses JMPs feature(?) where it just turns non-numeric values into missing values if the column is specified numeric.

*Edit for speed*

lista =  {"+1%", "2", "-45", ""};
dt = New Table( "Check Data",
  New Column( "Character", Character, "Nominal", Set Values( lista ) ),
  New Column("Numeric", Numeric, Set Values( lista )),
  invisible
);
if( ColNMissing(Column(dt, "Character")) ==  ColNMissing(Column(dt, "Numeric")),
       ret = as list(Column(dt, "Numeric") << get values),
       ret = Expr(print("Non-Convertable"));
);
close(dt, no save);
ret;

[#5]

Here's a variation on your original idea:

dt = New Table( "Check Data",
  Add Rows( 2 ),
  New Column( "Column 1", Character, "Nominal", Set Values( {"1", "2%", "-45"} ) )
);
a = Column(dt, "Column 1" ) << Get Values;
try(parse("["||concatitems(a,",")||"]"),"nope");

[#6]

and to suppress the log messages:

dt = New Table( "Check Data",
    Add Rows( 2 ),
    New Column( "Column 1", Character, "Nominal", Set Values( {"1", "2", "-45"} ) )
);
 
a = Column( dt, "Column 1" ) << Get Values;
cap = Log Capture(
    Try(
        Parse( "[" || Concat Items( a, "," ) || "]" );
        result = "ok";
    ,
        result = "bad"
    )
);
Show( result );

Re: how to bypass parse("a+b)") error?​ discussed logcapture and try.

edit: removed redundant assignment to result.

[#7 · Accepted solution]

Finally, to suppress the dialog, use the BatchInteractive function.  Notice there are two calls, one before, and one after.  If you leave JMP running in the BatchInteractive(1) state, you'll have a hard time closing JMP.

dt = New Table( "Check Data",
    Add Rows( 2 ),
    New Column( "Column 1", Character, "Nominal", Set Values( {"1", "2a", "-45"} ) )
);
a = Column( dt, "Column 1" ) << Get Values;
Batch Interactive(1);
cap = Log Capture(
    Try(
        Parse( "[" || Concat Items( a, "," ) || "]" );
        result = "ok";
    ,
        result = "bad"
    )
);
Batch Interactive(0);
Show( result );

[#8]

Craige@JMP​ Thanks, that worked a charm and I'm sure it something I will use again with other try statements.

[#9]

Probably should remove any missing values from the list as that will cause issues as well. 

Names Default to here(1);
a = {"1", "2", "", "45", "", "3"};
x = repeat(1, nitems(a));
x[loc(a, "")] = 0;
a = a[loc(x)];
Batch Interactive(1);
cap = Log Capture(
    Try(
        Parse( "[" || Concat Items( a, "," ) || "]" );
        result = "ok";
    ,
        result = "bad"
    )
);
Batch Interactive(0);
Show( result );