topic Re: Find rows that contains specific text in Discussions

Find rows that contains specific text

miguello — Fri, 15 Feb 2019 01:35:46 GMT

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!

Re: Find rows that contains specific text

miguello — Fri, 15 Feb 2019 02:02:36 GMT

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?

Re: Find rows that contains specific text

txnelson — Fri, 15 Feb 2019 02:22:31 GMT

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"))

Re: Find rows that contains specific text

gzmorgan0 — Fri, 15 Feb 2019 12:08:16 GMT

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);

Re: Find rows that contains specific text

Mark_Bailey — Fri, 15 Feb 2019 19:11:23 GMT

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.

Re: Find rows that contains specific text

miguello — Fri, 15 Feb 2019 22:01:03 GMT

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"? :)

Re: Find rows that contains specific text

miguello — Fri, 15 Feb 2019 22:01:58 GMT

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