Tables join: option to update main table

[hogi]

☑ cool new feature
☑ could help many users!

☐ removes a „bug“

☐ nice to have

☐ nobody needs it

#myTop10_2023a
(either this one or Speed up Tables/Update )

What inspired this wish list request? 

Tables/Join is a powerful tool to merge different data tables.

The only disadvantage: it generates a new data table instead of updating the main table.

I often have a table, with table dozens of table scripts which evolved over time - and Dashboards which refer to this table.

To widen the analysis, I want to add a column to the table - but Tables/Join generates a new table.

What is the improvement you would like to see? 

Please add an option in Tables/Join: Update main table.

If the option is enabled, the platform should update the main table instead of generating a new one.

If Jmp developers fear that Tables:Join gets too crowded by this option, they could hide the option behind a shift-click or invisible to other users.

Why is this idea important? 

There is no fast way to merge new columns to a data table. 

Table/Join is almost the perfect tool - but it generates a new table.

The additional setting will solve the issue.

more wishes submitted by  

[julian]

Hey @hogi ,

Is this the option you're looking for?

@julian 

[hogi]

Hi @julian - the name sound promising, yes
But I fear that this function differs from what I want to do:

What I want to do:

---

@hogi wrote:

1) I want to add a column to the table 
2) the platform should update the main table instead of generating a new one.

---

But according to the documentation:

which means, with Update main table with data from second table,

- no columns are added.

- a new data table is created (no change to std. Tables/Join behavior )

The first issue can be fixed by disabling the option Update main table with data from second table.

To get a solution for the second issue, I wrote this wish.

[julian]

Ah right you are, @hogi! I use Tables > Update for the functionality you're suggesting here and mistakenly assumed that this checkbox in Join was replicating that. Yes, this would be new functionality for Join. Am I correct in assuming that Tables > Update doesn't fit your situation because you would like to do an Inner or right outer join?

[hogi]

Hi @julian .
I am sure that we are not the only ones which were mislead by the promising name

Actually, Tables/Update would be my first choice - you do not even have to tell list the columns of the main table which you want to keep - you just keep the whole data table

Unfortunately, it already stands back oders of magnitude wrt. : 

---

@hogi wrote:

powerful tool to merge different data tables.

---

Speed up Tables/Update 
So actually, there are 2 options which will lead to a more or less similar result:
- solve the speed issue of Tables/Update (to make it as fast as Tables/Join)

- enable Tables/Join to update the main table (like Tables/Update can do)

[julian]

That's a frustrating performance difference. Do you have any missing values in the match columns for the tables you're joining? If so, you might try checking the box for "Ignore Missing Values" in Update, that can really speed things up, and seems to be the way Join is doing it by default. 

[hogi]

related posts:

Using Update() to add new rows into main table 

Join data into existing table 

Join, Concat and Update 

[FN]

One main application is when updating a time series data with new columns and rows. 

In these operations you have a time index based on UTC, but there is not a simple way to perform this in JMP.

[hogi]

The new Join platform could look like this:

Names Default To HERE(1);

dt = current data table();
dtList1 = Associative Array(get data table list());

likeUpdate=Function({},
	joinupdate[PanelBox(3)][checkBoxBox(3)] << set (1,1);
	joinupdate[PanelBox(3)][checkBoxBox(4)] << set (1,0);
	joinupdate[PanelBox(4)][checkBoxBox(1)] << set (1,0);
	joinupdate[PanelBox(4)][checkBoxBox(2)] << set (1,1);
	joinupdate[PanelBox(4)][checkBoxBox(3)] << set (1,1);
	joinupdate[PanelBox(4)][checkBoxBox(4)] << set (1,0);
	joinupdate[PanelBox(5)][checkBoxBox(1)] << set (1,1);
	NCols1= NItems(joinupdate[PanelBox(2)][ListBoxBox(1)]<< get items());
	foreach ({col},1::NCols1,	joinupdate[PanelBox(2)][ListBoxBox(1)] << set selected (col));
	joinupdate[PanelBox(5)][ButtonBox(1)] << click;
	joinupdate[PanelBox(6)][ButtonBox(2)] << click;
	
	
	dtList2 = Associative Array(get data table list());
	dtlist2 << remove (dtList1);
	dtNew=(dtList2 << get keys())[1];
	
	dt << select all rows() << delete  rows();
	dt << concatenate (dtNew, Append to first table);
	
	
);
Try(joinUpdate << close window());
joinupdate=new window("join+",dt<< join);
joinUpdate[PanelBox(6)][Lineup Box( 1 )] << prepend( mb = MouseBox( Button Box( "Update", likeUpdate() ) ) );
mb << settooltip( "acts like Update (selects the right CheckBoxes, triggers Join, merges data back into main table)" );

[hogi]

Disadvantages of this handmade approach:

• The Action is not logged by Advanced Log: Call a Platform and log the action 
• Row states are lost - or need to be mapped manually

[FN]

Since we are looking into improving the operation of joining tables, I wonder if we can include this fuzzy merge:

https://community.jmp.com/t5/JMP-Scripts/quot-Fuzzymerge-quot-join-tables-with-slightly-different/ta...