# Calculate/concatenate depending on field values

484
10
01-17-2023 04:40 PM
Occasional Contributor II

Hello-

I am trying to write an attribute rule that will calculate a field value by concatenating other fields. I have the basic arcade written, however, we have some variation in our table that could cause the straightforward statements to not work as I want. Here is what I need:

Prefix + Route + Week -- the field can only be 5 characters long total -- ex CC01S or D005N or CB005 or D0005

• Prefix could be one or two characters, ex: CC, D, CB
• Route has a value like 001, 005, 011
• Week = North or South-- I amusing the following to pull off only the first letter of the word: LEFT(\$feature.COMMERCIAL_WEEK,1)

My thought is that if I can identify whether or not the following is true I can create the new concatenated field:

• Identify if Week is null. THIS WORKS
``````// First determine if there is week information
// If there is, add 'week' sufix to it
var wmWeek = null;
if (!IsEmpty(\$feature.CASTERBOXCARDBOARD_WEEK)){
wmWeek = LEFT(\$feature.CASTERBOXCARDBOARD_WEEK,1);
}``````
• Identify if prefix value is one or two characters long. This would allow me to know how many leading zeros are needed from the route field. This is also the step where I get stuck. What I need to be able to write is something like:
• IF Prefix is >1 return Route = RIGHT(\$feature.Route,2)
• else IF Prefix is <1 return Route = RIGHT(\$feature.route,1)

Here is what I have working so far thanks to a couple of blog posts I found:

``````//works, getting closer- need to figure out the prefix when 2 characters and the route 0001 vs 001 vs 01

// First determine if there is week information
// If there is, add 'week' sufix to it
var wmWeek = null;
if (!IsEmpty(\$feature.CASTERBOXCARDBOARD_WEEK)){
wmWeek = LEFT(\$feature.CASTERBOXCARDBOARD_WEEK,1);
}

//Second determin if the prefix is one or two characters
var prefix = \$feature.CASTERBOXCARDBOARD_PREFIX
var route = \$feature.CASTERBOXCARDBOARD_ROUTE

if (prefix is >1){

}
//List of fields to combine
var parts =[\$feature.CASTERBOXCARDBOARD_PREFIX,\$feature.CASTERBOXCARDBOARD_ROUTE,wmWeek];

//loop through parts, if null/empty add to new array
var filteredparts =[];
for (var i in parts) {
var value = parts[i];
if (isEmpty(value)) continue;
filteredparts[Count(filteredparts)] =value
}
//Concatenate
return concatenate(filteredparts,"");``````

any help or ideas here would be great!

1 Solution

Accepted Solutions
Occasional Contributor II

Huge thanks to @JohannesLindner and @MikeMillerGIS  for helping me get this one going.

Here is my final that works as I needed. again, thank you!

``````//---------------------------------------------------------------------
//prefix + route + week
//check for empty prefix values
var prefix = IIf(
IsEmpty(\$feature.CASTERBOXCARDBOARD_PREFIX),
"",
\$feature.CASTERBOXCARDBOARD_PREFIX
)
// figure out if wmWeek is null so we know if we need to account for N/S in the concatenation character count
var week = IIf(
IsEmpty(\$feature.CASTERBOXCARDBOARD_WEEK),
"",
Left(\$feature.CASTERBOXCARDBOARD_WEEK, 1)
)
//find the length of the routename - 5 character limit - prefix characters - wmWeek character if present
var routename_length = 5 - Count(prefix) - Count(week)
//creates the concatenation
var routename = Right(\$feature.CASTERBOXCARDBOARD_ROUTE, routename_length)
return Concatenate([prefix, routename, week])``````
10 Replies
Occasional Contributor III

You can get the length of a string with the COUNT function, then you can use that length to pick which branch to take.

MVP Frequent Contributor

Does this do what you want?

``````var prefix = \$feature.CASTERBOXCARDBOARD_PREFIX
var week = IIf(
IsEmpty(\$feature.CASTERBOXCARDBOARD_WEEK),
"",
Left(\$feature.CASTERBOXCARDBOARD_WEEK, 1)
)
var route_length = 5 - Count(prefix) - Count(week)
var route = Right(\$feature.CASTERBOXCARDBOARD_ROUTE, route_length)
return Concatenate([prefix, route, week])``````

Have a great day!
Johannes
Occasional Contributor II

thank you for the response!  I think this is getting me closer... but I am getting an Invalid expression. Error on line 10. Expected array or feature set.

I adjusted what you shared above a bit, I needed the ROUTENAME field to be the 5 character limit. I am not sure if I have line 12 correct. should it be the route field (values of 001, 005 etc) or the Route Name (concatenated calculation field)?

``````//prefix + route + week
var prefix = \$feature.CASTERBOXCARDBOARD_PREFIX
// figure out if wmWeek is null or not so we know if we need to account for N/S in the concatenation or not
var week = IIf(
IsEmpty(\$feature.CASTERBOXCARDBOARD_WEEK),
"",
Left(\$feature.CASTERBOXCARDBOARD_WEEK, 1)
)
//find the length of the routename - 5 character limit - prefix characters - wmWeek character if present
var routename_length = 5 - Count(prefix) - Count(week)
//creates the concatenation
var routename = Right(\$feature.CASTERBOXCARDBOARD_ROUTE, routename_length)
return Concatenate([prefix, route, week])``````

thoughts on how to fix the Expected array or feature set error?

Esri Frequent Contributor

Could Prefix be None?  Count errors on that

Occasional Contributor II

no- prefix can be two characters or one character, but never empty

thanks!

Occasional Contributor II

Actually!!! When I added this in, it worked!!

Esri Frequent Contributor

Even though your data may enforce that it is either 1 or 2 characters, the validation generates a random record and that may not have the correct values.  So it is always safer to code defensively.

MVP Frequent Contributor

In line 12, you need to use the route field (the value that is already in the table, not the concatenated value you are calculating). You changed the variable name to routename, use that in line 13, too!

As Mike said, Count gives an error for null. Try replacing line 1 with this (this is the same null check that we already do for the week):

``````var prefix = IIf(
IsEmpty(\$feature.CASTERBOXCARDBOARD_PREFIX),
"",
\$feature.CASTERBOXCARDBOARD_PREFIX
)``````

Have a great day!
Johannes
Occasional Contributor II

Huge thanks to @JohannesLindner and @MikeMillerGIS  for helping me get this one going.

Here is my final that works as I needed. again, thank you!

``````//---------------------------------------------------------------------
//prefix + route + week
//check for empty prefix values
var prefix = IIf(
IsEmpty(\$feature.CASTERBOXCARDBOARD_PREFIX),
"",
\$feature.CASTERBOXCARDBOARD_PREFIX
)
// figure out if wmWeek is null so we know if we need to account for N/S in the concatenation character count
var week = IIf(
IsEmpty(\$feature.CASTERBOXCARDBOARD_WEEK),
"",
Left(\$feature.CASTERBOXCARDBOARD_WEEK, 1)
)
//find the length of the routename - 5 character limit - prefix characters - wmWeek character if present
var routename_length = 5 - Count(prefix) - Count(week)
//creates the concatenation
var routename = Right(\$feature.CASTERBOXCARDBOARD_ROUTE, routename_length)
return Concatenate([prefix, routename, week])``````