topic Re: Many to one Table Join in Discussions

Many to one Table Join

timothy_forsyth — Fri, 10 Nov 2023 17:50:25 GMT

Dear Sir/Madam,

I have two tables that I need joined

Two requests:

Request 1)

Table 1 has multiple entries per day

Table 2 has 1 entry per day

I would like to join the tables matching "Parameter Set Name and Timestamp"

to create a table like this. How do I do that?

Request 2)

Given the First two tables how do I join them so that for every given day I get only the last value for Parameter X as shown below

Any thoughts and suggestions?

Thanks, Timothy

Re: Many to one Table Join

jthi — Fri, 10 Nov 2023 17:53:46 GMT

Which JMP version you have? If you have JMP17 it is easiest to just have the tables open, go to Join tables menu and try out different options. Request 1 should be very simple join and for request 2 you might need to create Summary table before performing the join.

Also if you are able to provide example tables for us (anonymized or mock-up works), we can provide you with more accurate instructions.

Re: Many to one Table Join

timothy_forsyth — Fri, 10 Nov 2023 18:01:39 GMT

I have JMP 16. I did put mock up tables in my post. Are you able to see them?

Re: Many to one Table Join

jthi — Fri, 10 Nov 2023 18:04:44 GMT

Ok, if you have JMP16, you won't have access to preview tables, but you can enable "keep dialog open" option so if you mess something up, you still have the dialog open.

I can see the images, but those aren't really tables as I cannot open them in JMP or I cannot bring the data into JMP. If you could add them as JMP tables, it would make it much easier.

Re: Many to one Table Join

hogi — Fri, 10 Nov 2023 18:17:24 GMT

For option 2 there is a dirty hack:

Join always takes the first matching entry.

So with the option

- if you reverse the table orders (via sort descending) , the "last" - (now: first) value will stay.

So, less open tables - but a weird feeling if what I promise is really true :)

dt= new table("test", add rows(8),New Column( "Column 1",
set values([1 1 1 1 1 2 2 2])
)
);

dt <<  New Column( "Rank1",	set each value( Col Rank( 1,:Column 1 ) ));


dt2 = dt << Subset( All rows, Selected columns only( 0 ) );
dt2: Rank1 << set name("Rank2");

dt << Sort(By( :Rank1 ),Replace Table,Order( Descending, Descending ));
dt2 << Sort(By( :Rank2 ),Replace Table,Order( Descending, Descending ));

dt << Join(
	With( dt2 ),
	Merge Same Name Columns,
	Match Flag( 0 ),
	By Matching Columns( :Column 1 = :Column 1 ),
	Drop multiples( 1, 1 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 0 )
);

Re: Many to one Table Join

txnelson — Fri, 10 Nov 2023 18:47:47 GMT

Here are some visuals to support @jthi and @hogi suggestions

Issue 1

Tables=>Join Joining Table 2 to Table 1

Issue 2

Tables=>Sort Create a Virtual Column and call it Row.......the formula for the column is Row()

Then

Tables=>Join Join Table 1 to Table 2

Here is the JSL that will run the 2 issues

names default to here(1);
dt1 = New Table( "Table 1",
	Add Rows( 6 ),
	New Column( "Parameter Set Name",
		Character,
		Set Values(
			{"27109N5", "27109N5", "27109N5", "27109N5", "27109N5", "27109N5"}
		)
	),
	New Column( "Timestamp",
		Format( "yyyy-mm-dd", 12 ),
		Input Format( "yyyy-mm-dd" ),
		Set Values(
			[3600115200, 3600115200, 3600201600, 3600201600, 3600201600, 3600201600]
		)
	),
	New Column( "Parameter X",
		Set Values( [2.32, 1.21, 1.45, 1.45, 7.89, 2.04] )
	)
);

dt2 = New Table( "Table 2",
	Add Rows( 2 ),
	New Column( "Parameter Set Name",
		Character,
		Set Values( {"27109N5", "27109N5"} )
	),
	New Column( "Timestamp",
		Format( "yyyy-mm-dd", 12 ),
		Input Format( "yyyy-mm-dd" ),
		Set Values( [3600115200, 3600201600] )
	),
	New Column( "Parameter Y",
		Format( "Best", 12 ),
		Set Values( [21, 22] )
	)
);

// Join data tables
// → Data Table( "One to Many" )
Data Table( "Table 1" ) << Join(
	With( Data Table( "Table 2" ) ),
	Merge Same Name Columns,
	By Matching Columns(
		:Parameter Set Name = :Parameter Set Name, :Timestamp = :Timestamp
	),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 ),
	Output Table( "One to Many" )
);

// Create the Many to One table
// Sort data table
Data Table( "Table 1" ) << Sort(
	By(
		:Parameter Set Name,
		:Timestamp,
		Transform Column( "Row", Formula( Row() ) )
	),
	Replace Table,
	Order( Ascending, Ascending, Descending )
);

// Join Table 1 to Table 2
// Join data tables
// → Data Table( "Many to One" )
Data Table( "Table 2" ) << Join(
	With( Data Table( "Table 1" ) ),
	Merge Same Name Columns,
	By Matching Columns(
		:Parameter Set Name = :Parameter Set Name, :Timestamp = :Timestamp
	),
	Drop multiples( 1, 1 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 ),
	Output Table( "Many to One" )
);

Re: Many to one Table Join

timothy_forsyth — Fri, 10 Nov 2023 18:33:07 GMT

Thanks for the reply. Im a little fuzzy on creating a virtual column. Could you elaborate?

Thanks so much

Re: Many to one Table Join

hogi — Fri, 10 Nov 2023 18:40:04 GMT

create virtual column ~ transform column:

right click on one of the columns in the list and select row/row: