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
miguello
Level VI

Find rows that contains specific text

All,

 

I got stuck on a seemingly easy step.

 

My table is compiled from multiple CSV files. Where those files were previously concatenated, there is one service row that is empty except first three columns where it says something like "### Switching Logging: datetime" in the first column and then from which file in second coumn and to what file in third column. So, a trash row that I want to delete. The only common thing about those rows is word "Switching" in the first column. Plus the name of the first column has semicolon, something like index:date and I need to keep it that way. How do I find and delete those rows without creating new columns?

I tried something like this:

db_test << Get Rows Where (Contains("Switch", Column(1)));

But it doesn't work, apparently Contains() only takes columns that are refered to as :columnname. But since I have ":" in the name of my column so I cante refer to it as ":index:date". Even if I rename the column, it apparently doesn't work.

How to do this in one line?

Thanks! 

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Find rows that contains specific text

I think this may be the solution you are looking for:

Names Default To Here( 1 );
db_test  = Open( "$SAMPLE_DATA/big class.jmp" );


db_test << get rows where(contains(as column(3),"F"))
Jim

View solution in original post

6 REPLIES 6
miguello
Level VI

Re: Find rows that contains specific text

Ok, it works this way:

db_test << Get Rows Where (Contains(:"DataIndex:date", "Switch"));

However, if I put Column(1) instead of  :"DataIndex:date", it errors out. Any advice on how to refer to a column using its position only?

txnelson
Super User

Re: Find rows that contains specific text

I think this may be the solution you are looking for:

Names Default To Here( 1 );
db_test  = Open( "$SAMPLE_DATA/big class.jmp" );


db_test << get rows where(contains(as column(3),"F"))
Jim
gzmorgan0
Super User (Alumni)

Re: Find rows that contains specific text

Jim's suggestion is the best solution.  As an FYI, attached is legacy syntax that was commonly used prior to As Column(). You can use a column reference in a formula, however, you need empty brackets to represent the rows, the values of the column. 

 

Names Default To Here( 1 );
db_test  = Open( "$SAMPLE_DATA/big class.jmp" );


mycol =  Column("age");
xf = db_test << get rows where(contains(column(3)[],"F"));
xm = db_test << get rows where(contains(column("sex")[],"M"));
xa = db_test << get rows where( mycol[]== 13);
show (xf, xm, xa);

 

miguello
Level VI

Re: Find rows that contains specific text

Thanks! I remember there was some funky business with referring to columns, but I thought it was fixed. Apparently, it was fixed as "Column" --> "As Column"? :)
miguello
Level VI

Re: Find rows that contains specific text

Ok, all it took was changing Column to As Column. Awesome, thanks a lot!

Re: Find rows that contains specific text

I think that this syntax might work, too:

 

db_test << Get Rows Where (Contains(Column(1)[ ], "Switch"));

That is, use an empty subscript (square brackets) after the Column(1) function call.