Many to one Table Join

[#1]

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

[#2 · Accepted solution]

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.

[#3]

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

[#4]

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.

[#5 · Accepted solution]

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

[#6]

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

[#7]

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

Thanks so much

[#8 · Accepted solution]

create virtual column ~ transform column:

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