Choose Language Hide Translation Bar
Highlighted
Jacksmith12
Level IV

Merging a column from one JMP file to another JMP file

I have two JMP files which look something like this

 

JMP file 1:

Name Sex 0 Sam M 1 John M2 Larry M3 Mark M
JMP file 2:
Name Age Sex 0 Kelvin 51 M 1 Bob 26 M2 Larry 37 M3 Jerry 17 M6 Nick 68 M8 Sam 98 M9 Jax 78 M10 John 23 M11 Mark 47 M12 Jerry 68 M

 

My output should ideally be this:

 

Output: 

Name Age Sex 0 Sam 98 M 1 John 23 M2 Larry 37 M3 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??

7 REPLIES 7
Highlighted
txnelson
Super User

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

Jim
Highlighted
stan_koprowski
Community Manager Community Manager

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
Highlighted
Jacksmith12
Level IV

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

How can do I select option for editing joint?
Highlighted
stan_koprowski
Community Manager Community Manager

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

Highlighted
Jacksmith12
Level IV

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

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

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

 

 

Highlighted
stan_koprowski
Community Manager Community Manager

Re: 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

Article Labels

    There are no labels assigned to this post.