JSL select rows with certain string and make subset

achid03

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

txnelson

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