cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
UberBock
Level III

Selecting rows and then further selecting based on that

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) 

8 REPLIES 8
UberBock
Level III

Re: Selecting rows and then further selecting based on that

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.

txnelson
Super User

Re: Selecting rows and then further selecting based on that

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);
Jim
UberBock
Level III

Re: Selecting rows and then further selecting based on that

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

 

txnelson
Super User

Re: Selecting rows and then further selecting based on that

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.
Jim
UberBock
Level III

Re: Selecting rows and then further selecting based on that

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.

UberBock
Level III

Re: Selecting rows and then further selecting based on that

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?
UberBock
Level III

Re: Selecting rows and then further selecting based on that

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??

txnelson
Super User

Re: Selecting rows and then further selecting based on that

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);
Jim