cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
jmpandvba
Level II

Workaround to "update" function in script

Hello JMP community,

 

I have a data table that is dependent on another data table. I've tried updating it with the update function, but it seems to not do anything. Instead, what this script is doing is opening a new data table window with the selected subset instead of updating the current table. How can I get it to update the currently open datatable "dt" with the subset of "dtU"? Thanks!

 

// Open Sample Data 
dt = Open("KrF CDU Data table.jmp");
dtU = Open( "Exposure_Summary_Data.jmp" ); 

// Making subset of data
dtU << Clear Column Selection();
dtU << Select Where(contains("KrF", :Type));  
Column(dtU,"F23")<< Set Selected(1); 
dtU << Invert Column Selection; 
dt1 = dtU << Subset(Selected Rows(1),Selected Columns(1,:Wafer Code));

// Updating with subset
dt << Update(With(dt1),By Matching Columns(name==name),);
10 REPLIES 10
gzmorgan0
Super User (Alumni)

Re: Workaround to "update" function in script

Some of your JSL looks wrong or strange:

 

dtU << Select Where(contains("KrF", :Type));  

that should be

 

dtU << Select Where(contains( :Type, "KrF:));  

Also, this line two looks strange Selected Columns(1,:Wafer Code) and 

dt1 = dtU << Subset(Selected Rows(1),Selected Columns(1,:Wafer Code));

 You did not mention which table is dependent. By default, subset tables are not dependent. 

 

What does the Log file report? 

 

 

jmpandvba
Level II

Re: Workaround to "update" function in script

Whoops, meant to write that line as 

 

dt1 = dtU << Subset(Selected Rows(1),Selected Columns(1));

However, it still doesn't update dt ("KrF CDU Data table.jmp"). I am trying to update the KrF data table with data from the Exposure Table (dtU). 


This is what the log looks like:

Name Unresolved: name in access or evaluation of 'name' , name/*###*/

In the following script, error marked by /*###*/
Clear Log();
Clear Globals();
Close All( DataTables, "No Save" );
dt = Open( "KrF CDU Data table.jmp" );
dtU = Open( "Exposure_Summary_Data.jmp" );
dtU << Clear Column Selection();
dtU << Select Where( Contains( :Type, "KrF" ) );
Column( dtU, "F23" ) << Set Selected( 1 );
dtU << Invert Column Selection;
dt1 = dtU << Subset( Selected Rows( 1 ), Selected Columns( 1 ) );
dt << Update( With( dt1 ), By Matching Columns( name/*###*/ == name ) );

So there is some sort of error with the update line? I'm not sure, but that's what the log is reporting

mikedriscoll
Level VI

Re: Workaround to "update" function in script

try changing

dt << Update( With( dt1 ), By Matching Columns( name == name ) );

to

dt << Update( With( dt1 ), Match Columns( name== name ) );

Based on the commented out lines of my script, my previous attempt at joining used the former format and didn't work when I changed to update. Syntax is different

 

 

 

jmpandvba
Level II

Re: Workaround to "update" function in script

Tried the change, but instead of updating the KrF data table (dt), it only opens a new subset, and it does not update the table dt. Do I need to connect both to an odbc server? Both tables are still opening, so JMP definitely sees both tables.
mikedriscoll
Level VI

Re: Workaround to "update" function in script

Not sure if I'm interpreting what you're trying to do here correctly, but here is my interpretation, with some sample data.  You'll need to add a column called name after the stop() command.  This worked ok for me.

 

Edit: I think I misunderstood something. ... you mean you're trying to update a table that is locked because it is dependent, like a summary, JMP may force a new table. I'm not sure about that.

 

Clear Log();
Clear Globals();
//Close All( DataTables, "No Save" );
dt = Open( "$SAMPLE_DATA/Students.jmp" );
dtU = Open( "$SAMPLE_DATA/Big Class.jmp" );
dtU << Clear Column Selection();
dtU << Select Where( :age == 12 );
Column( dtU, "age" ) << Set Selected( 1 );
dtU << Invert Column Selection;
dt1 = dtU << Subset( Selected Rows( 1 ), Selected Columns( 1 ) );

stop();
// run up to here, create an extra column called name in the dt (students.jmp) with some names from dtU (big class), then run next line
dt << Update( With( dt1 ), By Matching Columns( name == name ) );

 

 

 

gzmorgan0
Super User (Alumni)

Re: Workaround to "update" function in script

Since we cannot see the tables, this is just a guess of things to try

  • Check that the column name exists in both, both have the same data type (likely character) and modeling type (likely nominal).
  • use :name==:name or dt:name == dt1:name
  • check if any of the columns in dt have functions, especially if the columns to be updated have functions. I think you have to remove the function beforte updating. 

That's a bit of troubleshooting ideas.

jmpandvba
Level II

Re: Workaround to "update" function in script

So I've checked all the data types for the matching columns, and they are the same: same order and same data types. None of the columns have functions as well. I've attached the data sets as an image and my current code. I am totally lost, so any help is most appreciated. Thank you all!

 

//Clean up 
Clear Log(); Clear Globals(); //Close All(DataTables,"No Save"); 

// Open Sample Data 

dtU = Open( "Exposure_Summary_Data.jmp" ); 

// Making subset of data
dtU << Clear Column Selection();
dtU << Select Where(contains(:Type,"KrF"));  
Column(dtU,"F23")<< Set Selected(1); 
dtU << Invert Column Selection; 
dt1 = dtU << Subset(Selected Rows(1),Selected Columns(1));

// Opening other data table and updating 
dt = Open("KrF CDU Data table 1.jmp");
dt << Update( With( dt1 ), Match Columns( dt:name == dt1:name ) ); //This is still not running
dt << Save( "KrF CDU Data table 1.jmp" );

 

mikedriscoll
Level VI

Re: Workaround to "update" function in script

In this line of code, "name" refers to the column name. You aren't showing all of the column names but do you definitely have a column called "name" in both?

dt << Update( With( dt1 ), Match Columns( dt:name == dt1:name ) ); //This is still not running

 Try doing this operation manually from the Tables->update menu.  When you get the output you want, look at the source script in the top left (right click on source, click to see the script) and inspect that code to help get exactly what you want into your JSL.

jmpandvba
Level II

Re: Workaround to "update" function in script

So I did what you said and manually updated the table, this is the associated code

 

Data Table( "KrF CDU data table 1" ) << Update(
With( Data Table( "Subset of Exposure_Summary_Data" ) ),
Match Columns( :Wafer Code = :Wafer Code )
)

So, I applied this to my code directly

dt << Update( With( dt1 ), Match Columns( dt:Wafer Code= dt1:Wafer code ) );

However, instead of updating all the rows, it just updates the first row and copies that row for all rows, as illustrated below. Do I need to loop it for all rows?