JSL select rows with certain string and make subset

[#1]

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

[#2]

Try this for your row selection

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

[#3]

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

[#4]

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.

[#5]

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

the table is already open after concatenating

[#6 · Accepted solution]

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