Extract Value Across Multiple Columns

[ChrisGAEG]

Hello, 

I have a value that can be housed in 4 different columns. Not only that but the value is within a text string. Here is an example of the string - 

1-12: ESCC02SPL01, 1-12

I am trying to write an evaluation or batch calculation that searches the 4 columns for the value (contains 'SPL') and and populates a new column with the middle string (in between spaces) as the return. Such that the example above would evaluate to 

ESCC02SPL01

Another detail is that I need the first instance of when SPL is in the value. So if it is present in columns 3 & 4, I need to take the value that is in column 3. 

So far I am trying to put the columns in a list and then use Find to iterate over the list. I am more of a Python person so I'm not exactly sure how to do this with Arcade. What I have so far is this 

var row1 = $feature.f0;
var row2 = $feature.f0spare;
var row3 = $feature.dead
var row4 = $feature.f1

var rowlist = [row1, row2, row3, row4]

var splitter = Find('SPL', rowlist, 0)

if (splitter > 0) {
  return splitter
};

But this is returning incorrect values. 

Any help or opinions on this is greatly appreciated. Thanks for reading. 

[kah7_uagis]

What value is it returning for your example?

[ChrisGAEG]

Find returns the index # of the string you supply it, so it wasn't quite right for what I was trying to do. 

[BHeist]

Instead of using a list to iterate over you could use the When() function in Arcade. This will let you test a series of conditional expressions. Then you can put your text logic as one of the returns for the expression(s).  I would also look into the Function Reference documentation for some other helpful functions to accomplish this>

https://developers.arcgis.com/arcade/function-reference/logical_functions/#when

// Custom Arcade Expression for Symbology
// Test to see if "Y" is in any of the three exceedance columns. If so,the 
// expression returns true and the point is symbololized as "Y". Otherwise, the expression
// returns false and the point is symbolized as "N". 

 
// Eceedance Fields

var exc1 =  $feature["As_1_Exc"]

 

var exc2 = $feature["As_2_Exc"]

 

var exc3 = $feature["As_3_Exc"]

 
// Testing which fields have a "Y" value and return True, else return False

var soilExceeds = When( exc1 == 'Y' || exc2 == 'Y' || exc3 == 'Y', 'Y', 'N')

 
//Return Values

return soilExceeds

[ChrisGAEG]

Okay I see. This is a great example, thank you. I'll just have to find the equivalent of str.contains for Arcade, then parse out the string I need based on a split index (which I think you can do in Arcade). 

[BHeist]

Great!

Yes, I think you could use the Split() function in Arcade to accomplish this? 

Otherwise if you have a constant number of characters then maybe a Mid()function could be used to grab it? 

https://developers.arcgis.com/arcade/function-reference/text_functions/#split

[JohannesLindner · Accepted solution]

var values = [
  $feature.f0,
  $feature.f0spare,
  $feature.dead,
  $feature.f1
  ]
for(var v in values) {
  var value = values[v]
  if(Count(value) > Count(Replace(value, "SPL", ""))) {
    value = Replace(value, ":", "")
    value = Replace(value, ",", "")
    return Split(value, " ")[1]
    // you return the first found column, the next columns won't be evaluated.
  }
}
// return a default value if no column contains a value with "SPL"
return null

[ChrisGAEG]

Wow this is great, thank you once again Johannes!