topic Re: Selecting rows and then further selecting based on that in Discussions

Selecting rows and then further selecting based on that

UberBock — Fri, 09 Jun 2023 23:36:08 GMT

I have a data table where I can select rows based on one columns result. I would like to then expand that selection based on a value contained in the previously selected rows.

I tried

dt = Current Data Table();
dr << select where( :ctrlrange > 0.23 );
h = Column( dt, "reagentkit_id" ) << get Values;
dt << select where( :reagentkit_id == h );

That didnt seem to work as JMP froze.

The data table could have a lot of lines.

The reason I want to do this is that certain times the ctrlrange is higher than 0.23 and I want all data from reagent (where the ctrl range may not have been higher than 0.23)

Re: Selecting rows and then further selecting based on that

UberBock — Thu, 27 Aug 2020 00:45:06 GMT

I saw the 'expand' in the select but could not figure out how to expand based on the previous selection from a value in another column of that selection.

Re: Selecting rows and then further selecting based on that

txnelson — Thu, 27 Aug 2020 02:43:39 GMT

I believe this is what you want

dt=current data table();

// You have a typo.....you are specifying dr and it should be dt....I assume
//dr<<select where(:ctrlrange>0.23);
dt<<select where(:ctrlrange>0.23);

h=Column(dt, "reagentkit_id")<<get Values;


dt<<select where(contains(h,:reagentkit_id);

Re: Selecting rows and then further selecting based on that

UberBock — Thu, 27 Aug 2020 13:11:04 GMT

It was a typo when I made the forum post.

I am trying

dt = Current Data Table();

dt << select where( :ctrlrange > 0.23 );

h = Column( dt, "reagentkit_id" ) << get Values;

dt << select where( Contains( h, :reagentkit_id ) );

Using it on a table that has over 2.9mil lines JMP does not respond. I take it this is an intensive process

Re: Selecting rows and then further selecting based on that

txnelson — Thu, 27 Aug 2020 13:12:12 GMT

If the list "h" is real long, then it will be a very intensive script. I would suggest that you create a summary data table grouping on your :reagentkit_id column. Make sure you have the "Link to original data table" selected, and then select all rows in the Summary Table. The link to the original table, will force all of the matches from the summary table to be selected in the original table.

Re: Selecting rows and then further selecting based on that

UberBock — Thu, 27 Aug 2020 17:47:52 GMT

Good Idea.

I tried this

dt = Current Data Table();
dt << select where( :ctrlrange > 0.23 );
h = Column( dt, "reagentkit_id" ) << get Values;

dt_rgt = dt << Summary(
    Group( :reagentkit_id ),
    N Catagories( :reagentkit_id ),
    Freq( "None" ),
    Weight( "None" ),
    Link to original data table( 1 )
);
dt_rgt << select where( Contains( h, :reagentkit_id ) );

It created a summary table that was linked. But it selected all rows in the original data table. Not kust the lines of data that had the reagentkit_id that was selected because one of its :ctrlrange was over 0.23.

If I comment out dt_rgt << select where( Contains( h, :reagentkit_id ) ); it works. Meaning that in a small table with 2,150 rows it picks out the 6 lines that have the ctrlrange being greater than 0.23 and it makes the summary table.

Re: Selecting rows and then further selecting based on that

UberBock — Thu, 27 Aug 2020 15:28:46 GMT

Should i select the lines with the ctrlrange greater than 0.23. Then subset into another table and then use that table to select the reagentkit_id from the original table?

Re: Selecting rows and then further selecting based on that

UberBock — Thu, 27 Aug 2020 17:48:43 GMT

dt = Current Data Table();
dt << select where( :ctrlrange > 0.23 );
dt << Subset( Output Table( "Sel" ), Selected Rows( 1 ), selected Columns( 0 ) );
dt_rgt = dt << Summary(
    Group( :reagentkit_id ),
    N Catagories( :reagentkit_id ),
    Freq( "None" ),
    Weight( "None" ),
    Link to original data table( 1 )
);

//h = Column( Sel, "reagentkit_id" ) << get Values;

dt_rgt << select where( Contains( Sel, :reagentkit_id ) );

Not sure how to indicate that it should select where looks at the Sel table for the values to select in the dt_rgt table.

Any ideas??

Re: Selecting rows and then further selecting based on that

txnelson — Thu, 27 Aug 2020 16:13:57 GMT

Your original premise does not work the way you think it does. <<get values does not just return values from selected rows.

Here is an example that works using a selection criteria, that should be pretty efficient.

names default to here(1);
dt=open("$SAMPLE_DATA/big class.jmp");

dt << select where(:weight <100);
dt << hide and exclude;

dt_rgt = dt << Summary(
	Group( :name ),
	Freq( "None" ),
	Weight( "None" ),
	link to original data table(1)
);

dt_rgt << delete rows;
dt << clear rowstates;
dt_rgt << select all rows;

close( dt_rgt, nosave);