topic Re: Field Calculator for not null fields in ArcGIS Pro Questions

Field Calculator for not null fields

AndrewIngall1 — Thu, 08 Nov 2018 18:38:49 GMT

I am trying to use the field calculator to concatenate various fields into one new field. The ArcGIS Arcade Guide gives the following example

Concatenate(['red', 'blue', 'green'], '/')

which gives red/blue/green. This works fine when all of the fields contain a value, but how can i concatenate only the fields that are not null

I am trying to concatenate address details , so for example

field1 = building name

field2 = building number

field3 = road name

field4 = area

field5 = town

in my example the building name or area could be null, so i end up with:-

,12,A Road,,Town - so i need to remove the additional field separators ','

Thanks for any help

Re: Field Calculator for not null fields

DanPatterson_Retired — Sat, 11 Dec 2021 13:40:30 GMT

Arcade... no, someone else can translate this example from python

# ---- pretend data, representing field values

field1 = 'building name'
field2 =  None
field3 = 'road name'
field4 = None
field5 = 'town'

# --- put them into a list... you could put the whole list in, I just simplied it

flds = [field1, field2, field3, field4, field5]

", ".join([i for i in flds if i is not None])

# ---- result

'building name, road name, town'‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Re: Field Calculator for not null fields

KoryKramer — Mon, 12 Nov 2018 15:08:52 GMT

Andrew Ingall‌ Did Dan's reply give you what you needed? If so, could you mark this as answered - maybe share back how you did this in Arcade?

If not, we should try to get an answer posted here. I know Xander Bakker‌ is handy with Arcade!

Re: Field Calculator for not null fields

AndrewIngall1 — Tue, 13 Nov 2018 07:46:24 GMT

@Kory Kramer - I actually went down the FME route to concatenate the field together - I could not get my head around how to do it in ArcGIS Pro with Arcade.

Re: Field Calculator for not null fields

XanderBakker — Sat, 11 Dec 2021 13:40:33 GMT

Just for future reference, you could use something like this in Arcade next time:

// replace the field names according to your data
var field1 = $feature["field1"];
var field2 = $feature["field2"];
var field3 = $feature["field3"];
var field4 = $feature["field4"];
var field5 = $feature["field5"];

var fields_in = [field1, field2, field3, field4, field5];
var fields_out = [];
for (var i = 0; i < Count(fields_in); i++) { 
    if (fields_in[i] != Null) {
        var j = Count(fields_out);
        fields_out[j] = fields_in[i];
    }
}

var label = Concatenate(fields_out, "/");
return label;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

This will only omit Null values and not empty strings.

I know Dan, this is much longer than the Python snippet you provided...

Re: Field Calculator for not null fields

DanPatterson_Retired — Tue, 13 Nov 2018 17:12:24 GMT

And longer than in Avenue too

Re: Field Calculator for not null fields

MichaelVolz — Fri, 05 Jun 2020 16:49:47 GMT

Dan:

In your python example, is None actually Null or some other way of showing empty data?

Re: Field Calculator for not null fields

JoshuaBixby — Sat, 06 Jun 2020 02:53:26 GMT

SQL Null gets mapped to Python None in cursors and the Field Calculator, so one can use Python None to test how SQL Null will behave with a code snippet.

Re: Field Calculator for not null fields

Andra — Fri, 30 Dec 2022 16:56:27 GMT

Hi, Xander

Thanks for the solution to this concatenation task. When i use your code in ArcGIS pro 2.9.5 field calculator tool in model builder, i am getting an error 'identifier expected' on line 17:

var field1 = $feature.utm_check; var field2 = $feature.township_match; var field3 = $feature.range_match; var field4 = $feature.section_match; var field5 = $feature.DPA_match; var field6 = $feature.county_match; var fields_in = [field1, field2, field3, field4, field5, field6]; var fields_out = []; for (var i = 0; i < Count(fields_in); i++) { if (fields_in[i] != Null) { var j = Count(fields_out); fields_out[j] = fields_in[i]; } } var label = Concatenate(fields_out, ","); return label;‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Can you provide any guidance on this syntax error please?

thanks, a

Re: Field Calculator for not null fields

Andra — Fri, 30 Dec 2022 18:40:31 GMT

Update - I was able to use Dan's python suggestion to concatenate the fields, but i am still curious about the above error if there is a simple solution. thanks!

', '.join([str(i) for i in [ !utm_check!, !township_match!, !range_match!, !section_match!, !DPA_match!, !county_match!] if i])

-a

Re: Field Calculator for not null fields

JoshuaBixby — Mon, 02 Jan 2023 16:13:34 GMT

Not sure how it could get much simpler. If the passing of field names is removed, the code boils down to:

', '.join([str(i) for i in [ ] if i])

That is pretty simple and compact. The bulk of the line is in field names, and you can't remove them.

Re: Field Calculator for not null fields

KevinN_CSJ — Thu, 08 Aug 2024 18:28:00 GMT

I know this thread is old, but in case anyone comes across this searching for this solution as I did, I got the code to work by replacing the full quotes "" with single quotes '' on line 17 when used in an AGOL custom field