398236_en-US

liliconcom

JSL select rows with certain string and make subset

Hi All,

 

I want to select certain rows that have string ended with "452" in column "Sample_ID" and make a subset containing these rows. I try to do like below but it didn't work

 

Names Default To Here( 1 );
Clear Log();
Clear Symbols();
Close All( Data Tables, NoSave );
Close All( Journals, NoSave );

// pick data table 1
dt1_dir = Pick Directory( "Select directory for data table 1" );
dt1_Name = Pick File( "Select data file 1", dt1_dir, {"JMP Files|jmp;jsl;jrn", "All Files|*"}, 1, 0, 0 );
Show( dt1_Name ); // show dt1_Name string in log
dt1 = Open( dt1_Name ); // reassign dt1 to data table from string dt1_Name
Show( dt1 ); // show data table dt1 in log to check

// pick data table 2
dt2_dir = Pick Directory( "Select directory for data table 2" );
dt2_Name = Pick File( "Select data file 2", dt2_dir, {"JMP Files|jmp;jsl;jrn", "All Files|*"}, 1, 0, 0 );
Show( dt2_Name );
dt2 = Open( dt2_Name );
Show( dt2 );

//concatenate two tables and concat_dt is the output table
concat_dt = dt1 << Concatenate( dt2, Output Table( "concat data table" ), Create Source Column );
Show( concat_dt ); //show concat_dt data table in log
Close( dt1, NoSave );
Close( dt2, NoSave ); //close both dt1 and dt2


//select specific sample_ID from concat_dt and make subset out of it
prep_dt = concat_dt << Subset(
 Row( concat_dt << get rows where( Regex( :Sample_ID, "71MT052800452" ) == :Sample_ID ) ),
 selected columns only( 0 ),

);

Close( concat_dt ); // close concat_dt

Data Blending and CleanupRe: JSL select rows with certain string and make subset

Here is a working example

names default to here(1);
// Create the concat data table
concat_dt = New Table( "concat data table",
 Add Rows( 4 ),
 New Column( "Source Table",
  Character,
  "Nominal",
  Set Values( {"A", "A", "B", "B"} )
 ),
 New Column( "Sample_ID",
  Character,
  "Nominal",
  Set Values(
   {"71MT052800452", "71MT052800193", "71MT052800452", "71MT052800120"}
  ),
  Set Display Width( 120 )
 ),
 New Column( "X_loc",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [-34005, -32288, -30571, -21986] )
 ),
 New Column( "Y_Loc",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [-26500, 26500, -26500, 26500] )
 ),
 New Column( "Z_Loc",
  Numeric,
  "Continuous",
  Format( "Best", 12 ),
  Set Values( [83.2, -20.3, 14.5, -78.3] )
 )
);

// Create the subset data

prep_dt = concat_dt << Subset(
 Rows( concat_dt << get rows where( Right( :Sample_ID, 3 ) == "452" ) ),
 selected columns only( 0 ), 
);

Close( concat_dt ); // close concat_dt

Re: JSL select rows with certain string and make subset

the data table "concat data table" looks like this.

 

the table is already open after concatenating

Re: JSL select rows with certain string and make subset

It appears to not to be able to find the data table called "concat data table".
Make sure that table has been created before you specify the data table function.

 

It would also be useful if you provided examples of your input data tables.

Re: JSL select rows with certain string and make subset

I tried it but it didn't work. I got this message in log:

 

 

Re: JSL select rows with certain string and make subset

Try this for your row selection

Row( concat_dt << get rows where( Right( :Sample_ID, 3 ) == "452" ) ),