topic Re: subset data table based on another data table in Discussions

subset data table based on another data table

joshua — Sat, 10 Jun 2023 23:19:30 GMT

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.

Re: subset data table based on another data table

txnelson — Thu, 24 Sep 2020 13:04:37 GMT

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;

Re: subset data table based on another data table

joshua — Fri, 25 Sep 2020 13:54:53 GMT

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 ?

Re: subset data table based on another data table

txnelson — Fri, 25 Sep 2020 15:12:22 GMT

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