BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
Brynn

Community Trekker

Joined:

Mar 19, 2018

Combining 2 Datasets by Matching 2 Variable Columns

I need to combine 2 large (15k rows) JMP datasets by matching up 2 variables (product_ID and week_ID). In other words, there is a unique record for each product_ID/week_ID combination. Each file contains different attribute columns. 

 

I'm just getting started with JSL so looking for script to get me started on this one task, otherwise I plan on pre-processing using VLOOKUP in excel. Thank you!

 

data1

product_IDweek_IDtypey1
11a10
12a24
13a33
21a2
22a8
23a14  


data 2

product_IDweek_IDtypesub-type1y3
11aR17.9
12aR14.8
13aR15.0
21aD21.7
22aD21.5
23aD20.1
31bR2.2
32bR1.1
33bR1.7
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Re: Combining 2 Datasets by Matching 2 Variable Columns

You want to use

     Tables==>Join

If you run the Join interactively, you can open the "Source" entry in the data table that is created from the Join, and it will contain the JSL that was used for the Join.

I entered your two sample data tables into my version of JMP.  They were named Untitled 230 and Untitled 231.  Below is the JSL from the "Source" property in the Joined table

Data Table( "Untitled 231" ) << Join(
	With( Data Table( "Untitled 230" ) ),
	By Matching Columns( :product_ID = :product_ID, :week_ID = :week_ID ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 1 ),
	Preserve main table order( 1 )
)
Jim
2 REPLIES 2
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Re: Combining 2 Datasets by Matching 2 Variable Columns

You want to use

     Tables==>Join

If you run the Join interactively, you can open the "Source" entry in the data table that is created from the Join, and it will contain the JSL that was used for the Join.

I entered your two sample data tables into my version of JMP.  They were named Untitled 230 and Untitled 231.  Below is the JSL from the "Source" property in the Joined table

Data Table( "Untitled 231" ) << Join(
	With( Data Table( "Untitled 230" ) ),
	By Matching Columns( :product_ID = :product_ID, :week_ID = :week_ID ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 1 ),
	Preserve main table order( 1 )
)
Jim
Brynn

Community Trekker

Joined:

Mar 19, 2018

Re: Combining 2 Datasets by Matching 2 Variable Columns

Thank you! Yes, this works. Can't believe I missed the "join" function in the tables menu.