Combining 2 Datasets by Matching 2 Variable Columns

Brynn

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_ID	week_ID	type	y1
1	1	a	10
1	2	a	24
1	3	a	33
2	1	a	2
2	2	a	8
2	3	a	14

data 2

product_ID	week_ID	type	sub-type1	y3
1	1	a	R	17.9
1	2	a	R	14.8
1	3	a	R	15.0
2	1	a	D	21.7
2	2	a	D	21.5
2	3	a	D	20.1
3	1	b	R	2.2
3	2	b	R	1.1
3	3	b	R	1.7

txnelson

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