topic Re: Selecting a row from a string column if it contains a certain word in Discussions

Selecting a row from a string column if it contains a certain word

S_Boilermaker — Fri, 14 Jun 2024 13:54:49 GMT

I have a set of data called value 2 that has been entered as a character with the units at the end. Some of them are entered as ml/min and some as ul/s, and I'd like to separate the ones that are in ul/s into a new column entirely. What function should I use to select the rows that contain a certain subset of a string (I was going to select the ones that contain "ul/s" to enter them into a new column and then delete them from the value 2 column). I thought the "contain" function would do what I want but it doesn't seem to work so far.

Re: Selecting a row from a string column if it contains a certain word

Jed_Campbell — Fri, 14 Jun 2024 14:41:47 GMT

current data table () << Select where (contains (:Value 2, "ul/s"))

This would be a way to do this using the contains() function. Another way to do this would be to use the Cols...Utilities...Text to Columns to separate the Value 2 column into multiple columns.

Re: Selecting a row from a string column if it contains a certain word

matth1 — Fri, 14 Jun 2024 14:58:39 GMT

If you want to script it, you can do this:

dt = data table( "your datatable name" );
dt << new column( "Value 3", Character );
for each row(
	dt:"Value 3"n = Char( If( Contains( :"Value 2"n, "ul/s" ) != 0, :Value 2, "" ) ); 
	dt:"Value 2"n = Char( If( Contains( :"Value 3"n, "ul/s" ) != 0, "", :Value 2 ) ); 
);

Just substitute your actual table name in the first line.

However, if you're trying to deal with mixed units and just want them to be comparable, you could try creating a new column with a formula like this:

Num( Regex( :Value 2, "[0-9]+" ) ) * 
If( Contains( :Value 2, "ul/s" ) != 0, 60 / 1000, 1 )

That creates a column where the ul/s data is converted to ml/min by extracting the numerical value (using Regex) then applying a conversion factor depending on the string in the cell. The resulting column is all in ml/min