How to do a "double match" with a JMP table and a Excel spreadsheet

Question

I'm probably not asking this question in the right way. I have a JMP table that needs to be matched with values that come from an Excel spreadsheet

an example of my JMP table would be the following (there are other columns, but they are not applicable):

ContainerSizeMeasured VolRequested VolCupLarge3.2 BowlSmall4.1 BucketMedium7.9

There is then a master table (the excel spreadsheet) that I would like to match with:

ContainerSizeRequested VolCupSmall1CupMedium2CupLarge3BowlSmall4BowlMedium5

BowlLarge6BucketSmall7BucketMedium8BucketLarge9

I would like to use the "master table" to populate the Requested Vol Column by matching the first two columns Container and Size. Is there a JSL function that can do this?

tdbae · Answer

The update function seems to do what I'm looking for. Thank you!

pmroz · Answer

I would use update to add the requested vol values.  You'll need to start without that column in your dataset.

// Create tables
dtc = New Table( "Container_tbl", Add Rows( 3 ),
	New Column( "Container", Character, "Nominal",
		Set Values( {"Cup", "Bowl", "Bucket"} ) ),
	New Column( "Size", Character( 16 ), "Nominal",
		Set Values( {"Large", "Small", "Medium"} ) ),
	New Column( "Measured Vol", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [3.2, 4.1, 7.9] ) ),
);

dtv = New Table( "Volume", Add Rows( 9 ),
	New Column( "Container", Character, "Nominal",
		Set Values( {"Cup", "Cup", "Cup", "Bowl", "Bowl", "Bowl", "Bucket", "Bucket",
			"Bucket"} ) ),
	New Column( "Size", Character( 16 ), "Nominal",
		Set Values( {"Small", "Medium", "Large", "Small", "Medium", "Large", "Small",
			"Medium", "Large"} ) ),
	New Column( "Requested Vol", Numeric, "Continuous", Format( "Best", 12 ),
		Set Values( [1, 2, 3, 4, 5, 6, 7, 8, 9] ) )
);

// Add requested volume
dtc << Update(
	With( dtv ),
	Match Columns( :Container = :Container, :Size = :Size ),
	Add Columns from Update table( :Requested Vol )
);

vince_faller · Answer

Just have JMP open the excel sheet and then use join with match on container and size.