Trouble converting columns from character to numeric

[#1]

Hello All,

this is my first post - I'm trying to automate data processing where I receive data in the form of excel spreadsheets... There's one tab in the workbook with all the relevant information.

JMP appears to import a lot of the data as character, not numeric, and I can convert the data to numeric manually in the table (usual way) but not with a script:

Any ideas?

thanks

Tom

Set current directory("C:\JDSU\ServerCopy\Data\Post-processing characterization\Flash I-V\AWSC");

dt=open(); // this is the only way I've found to let me select the relevant worksheet "Sort Data" in the .xls file

tables = ntable();

OpenDTs = List();

savethis = list();

closethese = list();

show(dt);

/* THis loop I thought would work to eliminate the open tables which I don't want, but it only closes a few, not all, of the unwanted tables

for(i=1, i<=tables, i++,

Insert into( openDTs, Data Table(i)<<GetName());

IF(Data table(i)<<GetName() =="Sort data",

  Continue();

  close(DataTable(i));

);

);

*/

dt<<delete rows({1,2,3,4,5,6,7,8,9}); // the first nine rows are not data - is there a way to capture this in the open() command?

column (1)<<set name("Wafer ID");

column (2)<<set name("Xref-Yref");

column (3)<<set data type("numeric"); // this doesn't work....

//column ({3,4,5,6,7,8,9,10,11,12,13}) <<set data type(numeric); //nor does this

col = New COlumn("FF_Calc", numeric);

col<<set formula(:vmp *:imp /:Voc /:Isc); // this returns errors as I can't change the offending columns to numeric.

col = NEw COlumn ("xref", character);

col = new column ("yref", character);

column("voc")<<set data type("numeric"); //still doesn't work

[#2]

Tom, I think you want to repost this in the JMP forum.

Art

[#3]

An example of correct syntax to change a character column to numeric:

column("voc") << Data Type( numeric );

column("voc") << Modeling Type( continuous ); // if you want continuous modeling type

or

dt:voc << etc...

Looks like your "if" statement needs a comma after "continue" ...

[#4]

Many thanks.

Do you have any idea why a for each row() loop works fine if I run it by highlighting and then Ctrl+R in the JSL window, but it doesn't work when executing as part of the whole script?

col = New COlumn("FF_Calc", numeric) << Modeling Type ( continuous );

col<<set formula(:vmp *:imp /:Voc /:Isc);

New Column("xref", Character, Ordinal, Formula(If(Right(Left(:Name("Xref-Yref"), 2), 1) == "-", Left(:Name("Xref-Yref"), 1), Left(:Name("Xref-Yref"), 2))), Set Property("Value Ordering", {"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16"}));

New Column("yref", Character, Ordinal, Formula(If(Left(Right(:Name("Xref-Yref"), 2), 1) == "-", Right(:Name("Xref-Yref"), 1), Right(:Name("Xref-Yref"), 2))), Set Property("Value Ordering", {"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16"}));

for each row(

if(:FF_Calc < 0, rowstate() = excluded state(1), :FF_calc > 1, rowstate() = excluded state(1), rowstate() = excluded state(0)

);

);

for each row(

if(dt:FF_Calc < 0, rowstate() = excluded state(1), dt:FF_Calc > 1, rowstate() = excluded state(1), rowstate() = excluded state(0)

);

); // neither of these work in the script, but if executed manually they work fine.  Do I have to select the table or something like that?

[#5 · Accepted solution]

You need to "run" the column formulas before using them.  Put this line before the for each row statement:

dt << run formulas;

where dt points to your data table.

[#6]

Bingo!

Many thanks!

[#7]

How does it not work? Do you get an error message or just the wrong result?

Anyway, the column variable col is not properly assigned. You have to send the modeling type message either in a separate statement or within New Column(). Once assigned you can use col instead of :FF_Calc.

E.g.

col = New Column( "FF_Calc", numeric, continuous );

col << set values( {-1, 0, 1, 2} );

For Each Row(

  If(

  col < 0, Row State() = Excluded State( 1 ),

  col > 1, Row State() = Excluded State( 1 ),

  Row State() = Excluded State( 0 )

  )

);