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):
| Container | Size | Measured Vol | Requested Vol |
| Cup | Large | 3.2 | |
| Bowl | Small | 4.1 | |
| Bucket | Medium | 7.9 | |
There is then a master table (the excel spreadsheet) that I would like to match with:
| Container | Size | Requested Vol |
| Cup | Small | 1 |
| Cup | Medium | 2 |
| Cup | Large | 3 |
| Bowl | Small | 4 |
| Bowl | Medium | 5 |
| Bowl | Large | 6 |
| Bucket | Small | 7 |
| Bucket | Medium | 8 |
| Bucket | Large | 9 |
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?
Just have JMP open the excel sheet and then use join with match on container and size.
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 )
);