253049_en-US

liliconcom

Merging a column from one JMP file to another JMP file

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

ScriptingRe: Merging a column from one JMP file to another JMP file

Hi @Jacksmith12,

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

Re: Merging a column from one JMP file to another JMP file

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

 

 

Re: Merging a column from one JMP file to another JMP file

Hi @Jacksmith12,

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

Re: Merging a column from one JMP file to another JMP fileI have multiple rows what if I just want to include 1 row from 2nd table in the first table??Re: Merging a column from one JMP file to another JMP fileHow can do I select option for editing joint?Re: Merging a column from one JMP file to another JMP file

Hi @Jacksmith12 

You could use Tables-->JMP Query Builder

 

JMP Query BuilderJMP Query Builder

 

Use a left-outer join 

 

All Matching Rows-Left Outer JoinAll 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

Re: Merging a column from one JMP file to another JMP file

Use

     Tables==>Update

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