479753_en-US

liliconcom

How to select Rows on a column with a RegEx

Hi, I want to select all the rows that start with the word "Insert" on a column that has a very complicated name: "Aligned_bin_on_ref(ref[+/-]strand:start-end".  I can select any rows using some of the other columns but the name of this one is somehow being interpreted as "code". 

I am also trying to use a Regex because the "values" on the column may contain more than one instance of the word "Insert" and I want to select only those that start with "Insert" 

This is the basic code that I have with some other code that I have tried commented out. Attached is a sample of the table.

I will appreciate any pointers, 

Thanks!

Alfredo

//----------------- INITIALIZE ---------------
Names Default To Here( 1 );

dt = Current Data Table ();
dt << Clear Select;


//RefCol= "Aligned_bin_on_ref(ref[+/-]strand:start-end";

// dt << Select Where (:Name("Aligned_bin_on_ref(ref[+/-]strand:start-end"), "Insert" );

SelRows = dt << Get Rows Where (RegEx(As Column("Aligned_bin_on_ref(ref[+/-]strand:start-end") , "^Insert") == "Insert");<BR />
//InsertDepth = dt << Subset( (SelRows) );

AnalyticsScriptingWindowsRe: How to select Rows on a column with a RegEx

dt << select where(
 Contains( :"Aligned_bin_on_ref(ref[+/-]strand:start-end"n, , "Insert" ) == 1
)

Re: How to select Rows on a column with a RegEx

 Hi Jim,

 

I am still getting a "Unresolved Column at row 1 in access or evaluation of 'Aligned_bin_on_ref(ref[+/-]strand:start-end' , As Column/*###*/()" 

 

I tried your solution with and without the "n" after the column name, just in case it was not a typo.

BTW I am using JMP 16.0.0

dt << select where(
 Contains( :"Aligned_bin_on_ref(ref[+/-]strand:start-end", ,"Insert" ) == 1 );

Re: How to select Rows on a column with a RegEx

You did not copy the JSL correctly.  You have 2 commas after the column name, and no trailing "n"

dt << select where(
 Contains( :"Aligned_bin_on_ref(ref[+/-]strand:start-end"n , "Insert" ) == 1
)

I just realized my code had 2 commas, which was an mistake....I have corrected my entry

Re: How to select Rows on a column with a RegEx

Thank you very much Jim.  After I added the missing ")" parenthesis is working like a charm.  This is going to save me quite a few hours. Cheers!