topic Re: Workaround to "update" function in script in Discussions

Workaround to "update" function in script

jmpandvba — Tue, 07 Aug 2018 22:41:41 GMT

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

Re: Workaround to "update" function in script

gzmorgan0 — Wed, 08 Aug 2018 03:35:21 GMT

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?

Re: Workaround to "update" function in script

jmpandvba — Wed, 08 Aug 2018 15:44:22 GMT

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

Re: Workaround to "update" function in script

mikedriscoll — Wed, 08 Aug 2018 17:54:57 GMT

try changing

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

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

Re: Workaround to "update" function in script

jmpandvba — Wed, 08 Aug 2018 18:08:38 GMT

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.

Re: Workaround to "update" function in script

mikedriscoll — Wed, 08 Aug 2018 18:36:26 GMT

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

Re: Workaround to "update" function in script

gzmorgan0 — Wed, 08 Aug 2018 19:45:32 GMT

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.

Re: Workaround to "update" function in script

jmpandvba — Sat, 11 Aug 2018 03:32:27 GMT

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

Re: Workaround to "update" function in script

mikedriscoll — Fri, 10 Aug 2018 17:34:44 GMT

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.

Re: Workaround to "update" function in script

jmpandvba — Sat, 11 Aug 2018 03:33:08 GMT

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?

Re: Workaround to "update" function in script

gzmorgan0 — Sat, 11 Aug 2018 02:21:55 GMT

Match needs to specify as many columns that make this unique. So you might need match by multiple columns, for example :Experiment name == :Experiment name, :Stack==:Stack, :Wafer Code==:Wafer Code, :Metrology Recipe==:Metrology.

These are just suggestions.

You need to choose which columns make each row unique, then match accordingly.