BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
Highlighted
miguello

Community Trekker

Joined:

Jan 27, 2016

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

Joined:

Jun 22, 2012

Solution

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
6 REPLIES 6
miguello

Community Trekker

Joined:

Jan 27, 2016

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

Joined:

Jun 22, 2012

Solution

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

Joined:

Jul 25, 2016

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

Community Trekker

Joined:

Jan 27, 2016

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

Community Trekker

Joined:

Jan 27, 2016

Re: Find rows that contains specific text

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

Staff

Joined:

Jun 23, 2011

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.

Learn it once, use it forever!