I have two JMP files which look something like this JMP file 1: Name Sex 0 Sam M 1 John M 2 Larry M 3 Mark M JMP file 2: Name Age Sex 0 Kelvin 51 M 1 Bob 26 M 2 Larry 37 M 3 Jerry 17 M 6 Nick 68 M 8 Sam 98 M 9 Jax 78 M 10 John 23 M 11 Mark 47 M 12 Jerry 68 M My output should ideally be this: Output: Name Age Sex 0 Sam 98 M 1 John 23 M 2 Larry 37 M 3 Mark 47 M I have tried join and merge but the Rows values doesn't line up. I want to automate the steps in jsl script. Any suggestions please??

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Merging a column from one JMP file to another JMP file

Jackie_

I have two JMP files which look something like this

JMP file 1:<BR />  <BR />  <SPAN class="typ">Name</SPAN>       <SPAN class="typ">Sex</SPAN>
<SPAN class="lit">0</SPAN>   <SPAN class="typ">Sam</SPAN>      <SPAN class="pln"> M  </SPAN><SPAN class="lit">1</SPAN>   <SPAN class="typ">John</SPAN>     <SPAN class="pln"> M</SPAN><SPAN class="lit">2</SPAN>   <SPAN class="typ">Larry</SPAN>    <SPAN class="pln"> M</SPAN><SPAN class="lit">3</SPAN>   <SPAN class="typ">Mark</SPAN>     <SPAN class="pln"> M
<BR />JMP file 2:
    </SPAN><SPAN class="typ">Name</SPAN>    <SPAN class="typ">Age</SPAN>    <SPAN class="typ">Sex</SPAN>
<SPAN class="lit">0</SPAN>   <SPAN class="typ">Kelvin</SPAN>   <SPAN class="lit">51</SPAN><SPAN class="pln">     M  </SPAN><SPAN class="lit">1</SPAN>   <SPAN class="typ">Bob</SPAN>      <SPAN class="lit">26</SPAN><SPAN class="pln">     M</SPAN><SPAN class="lit">2</SPAN>   <SPAN class="typ">Larry</SPAN>    <SPAN class="lit">37</SPAN><SPAN class="pln">     M</SPAN><SPAN class="lit">3</SPAN>   <SPAN class="typ">Jerry</SPAN>    <SPAN class="lit">17</SPAN><SPAN class="pln">     M</SPAN><SPAN class="lit">6</SPAN>   <SPAN class="typ">Nick</SPAN>     <SPAN class="lit">68</SPAN><SPAN class="pln">     M</SPAN><SPAN class="lit">8</SPAN>   <SPAN class="typ">Sam</SPAN>      <SPAN class="lit">98</SPAN><SPAN class="pln">     M</SPAN><SPAN class="lit">9</SPAN>   <SPAN class="typ">Jax</SPAN>      <SPAN class="lit">78</SPAN><SPAN class="pln">     M</SPAN><SPAN class="lit">10</SPAN>  <SPAN class="typ">John</SPAN>     <SPAN class="lit">23</SPAN><SPAN class="pln">     M</SPAN><SPAN class="lit">11</SPAN>  <SPAN class="typ">Mark</SPAN>     <SPAN class="lit">47</SPAN><SPAN class="pln">     M</SPAN><SPAN class="lit">12</SPAN>  <SPAN class="typ">Jerry</SPAN>    <SPAN class="lit">68</SPAN><SPAN class="pln">     M</SPAN>

My output should ideally be this:

Output: 

    <SPAN class="typ">Name</SPAN>    <SPAN class="typ">Age</SPAN>    <SPAN class="typ">Sex</SPAN>
<SPAN class="lit">0</SPAN>   <SPAN class="typ">Sam</SPAN>      <SPAN class="lit">98</SPAN><SPAN class="pln">     M  </SPAN><SPAN class="lit">1</SPAN>   <SPAN class="typ">John</SPAN>     <SPAN class="lit">23</SPAN><SPAN class="pln">     M</SPAN><SPAN class="lit">2</SPAN>   <SPAN class="typ">Larry</SPAN>    <SPAN class="lit">37</SPAN><SPAN class="pln">     M</SPAN><SPAN class="lit">3</SPAN>   <SPAN class="typ">Mark</SPAN>     <SPAN class="lit">47</SPAN><SPAN class="pln">     M</SPAN>

I have tried join and merge but the Rows values doesn't line up. I want to automate the steps in jsl script.

Any suggestions please??

Accepted answers

All comments

txnelson

Use

Tables==>Update

with file 1 being the base table, and file 2 the secondary file, match on column Name

stan_koprowski

Hi @Jackie_

You could use Tables-->JMP Query Builder

JMP Query Builder

Use a left-outer join

All Matching Rows-Left Outer Join

New SQL Query(
	Version( 130 ),
	Connection( "JMP" ),
	JMP Tables( ["Table 1" => "_MEMORY_", "Table 2" => "_MEMORY_"] ),
	QueryName( "SQLQuery3" ),
	Select( Column( "Name", "t1" ), Column( "Age", "t1" ), Column( "Sex", "t1" ) ),
	From(
		Table( "Table 1", Alias( "t2" ) ),
		Table(
			"Table 2",
			Alias( "t1" ),
			Join(
				Type( Left Outer ),
				EQ( Column( "Name", "t1" ), Column( "Name", "t2" ) ) &
				EQ( Column( "Sex", "t1" ), Column( "Sex", "t2" ) )
			)
		)
	)
) << Run

How can do I select option for editing joint?

Jackie_

I have multiple rows what if I just want to include 1 row from 2nd table in the first table??

stan_koprowski

Hi @Jackie_,

you can edit the join after selecting the tables by clicking on the edit join button ( Venn diagram icon )

Additional instructions on using JMP Query Builder and editing the join conditions.

cheers,

Stan

Jackie_

What if I have multiple Columns in the 2nd Table and I just want to merge a specific column from Table 2 into Table 1

stan_koprowski

Hi @Jackie_,

To limit the rows you can use a filter.

Refer to the section in the documentation on selecting filters to import a subset of the data.

Let us know if that provides what you need.

cheers,

Stan