subset data table based on another data table

joshua

Hi,

 

Lets say we have main data table and there is another data table we want to use to subset the main one + add some conditionals during this process;

 

main table

Another data table

 

 

How can go through each row of Label column in main table to get the rows matched to subset tables rows (L1, L3, L5) and get rows when group == K and Number == 2

 

So the output table should look like 

 

Please provide the solution with for loop or whatever is out there and don't use and short list like list = {K1, K3, K5} because in my real data I have 1000's of rows.How to select rows based on multiple criteria? 

 

subset_table.jmp

main_table.jmp

txnelson

Here is a sample of one way of doing what you want.

I recommend you take the time and read the Using JMP and Scripting Guide documentation from the JMP Documentation Library, under the Help pull down menu.  It will open the window on the various ways to use the objects within JMP and to not be limited to thinking that every thing needs to be coded in a traditional programming methodology.

names default to here(1);
dt1=data table("main_table");
dt2=data table("subset_table");
dt2b = dt2 << subset(selected rows(0), columns(:label));

dtFinal = dt1 << Join(
	with(dt2b),
	Match Flag( 1 ),
	By Matching Columns( :Label = :Label ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 1 ),
	Preserve main table order( 1 ),
	merge same name columns( 1 )
);

close( dt2b, nosave );

dtFinal << select where(:match flag == 3 | (:Group == "K" & :Number == 2) );
dtFinal << invert row selection;
dtFinal << delete rows;