cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar

Tables join: option to update main table

☑ cool new feature
☑ could help many users!

☐ removes a „bug“

☐ nice to have

☐ nobody needs it

 

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

as we have a workaround for Tables/Update, also this one is no longer needed

 

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  hogi_2-1702196401638.png

14 Comments
julian
Community Manager
Status changed to: New

Hey @hogi ,

Is this the option you're looking for?

julian_0-1687607724792.png

@julian 

hogi
Level XII

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:

hogi_0-1687626890126.png

 

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
Community Manager

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
Level XII

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
Community Manager

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
Level XII
FN
Level VI

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
Level XII

The new Join platform could look like this:

hogi_0-1687982700175.png

 



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
Level XII

Disadvantages of this handmade approach:

FN
Level VI

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...