Best Practices for Repeated Joins/Updates in JMP Scripts

[#1]

Hi all,

I’m running a JSL script that performs Update/Join operations between calculation tables and filtered data tables. I noticed the following behavior:

1. When the script runs multiple times, JMP automatically renames duplicate columns to avoid name collisions, e.g.,

   Batch Number -   Batch Number of table  1, Batch Number of table 2

2. Even if I close the tables from the panel, the variables (column objects) still exist in memory, so re-running the script creates additional duplicate columns.

3. My current workaround is to exit JMP and restart before running the script to prevent duplicate columns.

Questions for the community:

• Are there best practices for repeated Update or Join operations that avoid generating duplicate columns?

• Is there a recommended way to “clean up” tables/variables after a script run so subsequent runs do not create duplicates?

• Are there functions or JSL patterns to safely rename or overwrite columns without generating extra copies?

Any suggestions or strategies would be greatly appreciated!

I also tried this code to handle them dynamically but didn't worlk 

RenameColumn = Function({dt, oldNamePattern, newName},
    colList = dt << Get Column Names;       // returns a column list object
    n = N Items(colList);                   // number of columns

    For(i = 1, i <= n, i++,
        c = colList[i];                     // get the column name as string
        If(Contains(c, oldNamePattern),
            Column(dt, c) << Set Name(newName);
            Break();                         // optional: stop after first match
        );
    );
);

[#2]

What do you wish to do to those columns? If they already exist, don't update/re-add them? Can you provide example script with data which community could look into? 

[#3]

I am curious to see the details behind the described issue.

esp.  the variables (column objects) still exist in memory, so re-running the script creates additional duplicate columns.

Besides this, please note:

For Tables/Update, such an issue is caused by a new functionality in JMP19:
Allow Update platform to create new columns if they already exist in the table to be updated 

The additional functionality is cool, but people did not consider the collateral effect!

collateral effect: 
With JMP19, it is not possible anymore to use the function in the mode:
"please add columns which are not yet in the target table".
further details: Tables/Update changed! 

Scripts behave differnetly and the uer will get tables like this one: 
(the additional columns are useless - they pollute the table)

I hope the developers understand the issue and fix it with the next release.

a possible solution : 2 modes
  automatic tables/update 

1. use the new approach and add a column (with a postfix) IF (!!!!) the user selects the column
   
2. don't use the trick in the Mode "All":
   
   
   -> will allow again 
   "add columns which are not yet in the target table"
   (like in previous version s of JMP).

... alternative: an additional setting to enable/disable the new mode.
To guarantee backward compatibility: disable the setting by default.

[#4]

Code: 

// Filtering Table A using RequiredBatches
FilteredA = TableA << Join(
	With(RequiredBatches),
	By Matching Columns(:Batch Number = :Batch Number),
	Drop Multiples(0,0),
	Include Matches(0,0),
	Output Table("FilteredA"),
	Invisible(0)
);

// Filtering Table B using RequiredBatches
FilteredB = TableB << Join(
	With(RequiredBatches),
	By Matching Columns(:Batch Number = :Batch Number),
	Drop Multiples(0,0),
	Include Nonmatches(0,0),
	Output Table("FilteredB"),
	Prevent Name Collision(1),
	Invisible(0)
);

// Rename joined key columns
Column(FilteredA, "Batch Number of TableA") << Set Name("Batch Number");
Column(FilteredB, "Batch Number of TableB") << Set Name("Batch Number");

If I run this script a second time without restarting JMP, the following happens:

• JMP automatically appends suffixes to table names:

FilteredA → FilteredA 2
FilteredB → FilteredB 2

• JMP also renames duplicate key columns:

  Batch Number of TableA --> Batch Number of TableA 2
  Batch Number of TableB  --> Batch Number of TableB 2
  

I have also tried using for all tables at the beginning of the script, this seems to work on mac as I do not face any issues but windows I think it is not working or really confused on the behaviour. 

Try(TableA << Close(No Save));

Even after closing from the JMP Home window list using 'Close All but this' on my script [where tables are displayed] , the tables are closed but probably variables are still in memory. So, I am not sure how to deal with this ? 

Would this help or any other way that doesn't involve repurposing the code :( 

Clear Symbols(); 

[#5]

Where is

 Prevent Name Collision(1)

coming from? 

Do the join interactively in JMP Join platform, make sure it looks like you want it to look like in the Preview, run it and copy the script from enhanced log.

[#6]

Have you tried to activate  "Update main table with data from second table"

[no new columns - update any matching row]

or "Match same name column"?

[similar to the first one - but adds new columns.]

[#7]

It sounds like you need to use the "Output Columns" function in Join, so that you're not pulling in duplicate data. Unless you pull in columns of the same name, using "Output Columns" won't produce the "... of table 2" suffix on the column names.

In Update, you would select the columns to Add, so it doesn't pull in the duplicate column names.

[#8]

yes, but this would be impractical for large list of columns

[#9]

---

@zetaVagabond1 wrote:

yes, but this would be impractical for large list of columns

---

this is a reply to .... ?