Select to view content in your preferred language

Calculate field

101
1
2 weeks ago
Ainosez
New Contributor II

Hello,

I need to calculate the number of fields that contain a specific text. In excel it would be something like this:
=CONTAR.SI(A2:AG2;" NO OK")
Can you tell me what the expression with Arcade would be?

Thanks

1 Reply
jcarlson
MVP Esteemed Contributor

You could just do Select By Attributes and look at the number of selected features, couldn't you? But to do it in Arcade, there isn't a direct equivalent to that function. We can recreate it with GroupBy and some SQL:

sql = `CASE WHEN field1 = 'NO OK' THEN 1 ELSE 0 END +
  CASE WHEN field2 = 'NO OK' THEN 1 ELSE 0 END +
  CASE WHEN field3 = 'NO OK' THEN 1 ELSE 0 END +
  CASE WHEN field4 = 'NO OK' THEN 1 ELSE 0 END`

var grouped = GroupBy(
  $layer, // or some FeatureSetBy… function, depends on how you're pulling this data into your expression
  'objectid',
  { name: 'no_ok_count', expression: sql, statistic: 'SUM' }
)
- Josh Carlson
Kendall County GIS