How do i change column format in JSL or deal with the Send Expects Scriptable Object error

[#1]

I am working on joining (2) tables in a JSL script.  The issue i'm having is with saving the newly joined tables.  There are (2) issues.

The first issue is that the Data columns of (Test2) are coming in to JMP as Character/Nominal and need to be Numeric/Continuous.  When i change or create the Excel spreadsheet column format to numeric i get the following error at the dt3 <<save as line of code.

Send Expects Scriptable Object in access or evaluation of 'Send' , dt3 << /*###*/save as("B:\JoinTest1_2.jmp") /*###*/

When i try to format the JMP data table column format in JSL i get the same error during the save as instruction.

I have searched this for hours and just cant figure it out.

Here is my code.  Any help would be greatly appreciated.

Names Default To Here( 1 );
dt1 = open("B:\Test1.jmp");
dt2 = Open("B:\Test2.jmp",
	Worksheets( "WorkSheet1" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 2 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
);
column(dt2,"Column1") << data type(Numeric)<<Modeling Type(Continuous)<<Format(Best,12);
column(dt2,"Column2") << data type(Numeric)<<Modeling Type(Continuous)<<Format(Best,12);
column(dt2,"Column3") << data type(Numeric)<<Modeling Type(Continuous)<<Format(Best,12);
column(dt2,"Column4") << data type(Numeric)<<Modeling Type(Continuous)<<Format(Best,12);

dt3 = Data Table( dt1 ) << Join(
	With( Data Table( dt2) ),
	By Matching Columns( :Column1 ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 0 ),
	Preserve main table order( 1 ),
);

Close (dt1, no save);
Close (dt2, no save);

//dt3 << select where(:Sample ID ==0);
//dt3 << delete rows();
dt3 << save as ("B:\JoinTest1_2")

[#2]

There are a couple of issues that I see.  I am not sure if dt2 is opening a .jmp data table, or an Excel file.  You have specified that dt2 is a .jmp file, but have encluded Excel specifications.

Next, the proper form of the "By Matching Columns" code should be :Column1 = :Column1........but as it is, if both columns exist in the data tables then there should not be an issue

I have added in a couple of statements to ensure that the data table dt3 exists both before and after you close the data tables dt1 and dt2.

Run the code and see what the JMP Log has printed out

Names Default To Here( 1 );
dt1 = open("B:\Test1.jmp");
dt2 = Open("B:\Test2.jmp", // Is this a JMP table or an Excel..... the .jmp extension seems strange here
	Worksheets( "WorkSheet1" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 1 ),
		Data Starts on Row( 2 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
);
column(dt2,"Column1") << data type(Numeric)<<Modeling Type(Continuous)<<Format(Best,12);
column(dt2,"Column2") << data type(Numeric)<<Modeling Type(Continuous)<<Format(Best,12);
column(dt2,"Column3") << data type(Numeric)<<Modeling Type(Continuous)<<Format(Best,12);
column(dt2,"Column4") << data type(Numeric)<<Modeling Type(Continuous)<<Format(Best,12);

dt3 = Data Table( dt1 ) << Join(
	With( Data Table( dt2) ),
	By Matching Columns( :Column1 = :Column1 ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 0 ),
	Preserve main table order( 1 ),
);

// Make sure it created the data table
show("before closes ", dt3);

Close (dt1, no save);
Close (dt2, no save);

// Make sure after the initial tables are closed that dt3 is still there
show("after closes ", dt3);

//dt3 << select where(:Sample ID ==0);
//dt3 << delete rows();
dt3 << save as ("B:\JoinTest1_2");

[#3]

Yes you are correct dt2 is an excel file, sorry this code is generic to what i'm doing with actual files.

i do have two columns that have the same title (sample ID).

The code performs the same way, when opening dt2 the data columns are not numeric/continuous and if i try to change the column parameters in the script i get the error above. How does JMP interpret excel data with a column header, could it be using the text in the column header to set the parameters?

[#4]

Ok i've stumbled upon a partial solution, the spreadsheet for both dt1 and dt2 have both character data and numeric data, although both excel files are formatted as "General" in excel, for whatever reason the dt2 table doesn't like that and forced me to format the numeric columns as "number" data.

So there is a work around, however i would like to know why the script doesn't seem to work when changing the parameters programatically.

[#5 · Accepted solution]

I think I see the problem.  When you specify

column(dt2,"Column1") << data type(Numeric)<<Modeling Type(Continuous)<<Format(Best,12);

JMP is looking for a column named "Column1".....Yes....Excel will set column names from the Excel table, because it is specified in the definition of how to read the Excel file

Has Column Headers( 1 )

So if you want to change the data type, etc. using the column position, you need to specify

column(dt2,1) << data type(Numeric)<<Modeling Type(Continuous)<<Format(Best,12);

[#6]

That worked like a champ, so column number is the correct syntax not column name. Bah, i should have been able to figure that out.

Thank you!

[#7]

I strongly suggest that you get in the habit in going to the Scripting Index to get the description of, and examples of the different functions you are using.

     Help==>Scripting Index 

Or to just get a description, once you type in a function name, with the starting "(" you can hover over the function name and it will display a description of the function