Calculate field using Arcade expression

Bryan_Wade

I have a feature layer with a mishmash of values that should be the same.  IE:  Features that should be marked as "Reviewed" might be "Reviewed" or "reviewed", resulting in a legend with 4x classes instead of two? 

This issue occurred when I updated and republished my survey. It was suggested I recalculate the field using an arcade expression. I have never done this before and was hoping someone could give me an idea of how to go about it.

 

jcarlson

If you go to the Data tab of your item and look at the Table, clicking a field heading lets you open up the Field Calculator. I would suggest using SQL for this, but either SQL or Arcade will work.

Arcade:

var s = $feature.status

return When(
    Find('ubmitted', s) != -1, 'Submitted',
    Find('eviewed', s) != -1, 'Reviewed',
    s
)

By using Find, we can tell our expression to return "Submitted" any time it finds 'ubmitted' in the field value and so on, which would account for capitals, extra spaces at the beginning / end.

In SQL, we can use LIKE, which is a bit more straightforward to me, but works on the same principal:

CASE
WHEN status LIKE '%ubmitted%' THEN 'Submitted'
WHEN status LIKE '%eviewed%' THEN 'Reviewed'
ELSE status
END

In both cases, we leave unmatched values at their initial value. The lists of conditions can easily be expanded for other statuses.