subset data table based on another data table

[#1]

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? 

[#2 · Accepted solution]

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;

[#3]

Excellent! I have to comment in some point. Manual is not enough. I scan through the manual and there is no close example case to my question. Do you have better source that we can find some use cases of the functions ?

[#4]

Scanning the manual doe not work...you need to read the manual...it provides the structures for you to build on