696852_en-US

liliconcom

Many to one Table Join

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

 

 

 

 

Content OrganizationData Blending and CleanupWindowsRe: Many to one Table Join

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

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

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

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

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

create virtual column ~ transform column:

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

 

 

 

 

Re: Many to one Table Join

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