Calculate/concatenate depending on field values

[VanessaSimps]

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!

[DavidSolari]

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

[JohannesLindner]

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])

[VanessaSimps]

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? 

[MikeMillerGIS]

Could Prefix be None?  Count errors on that

[VanessaSimps]

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

thanks!

[VanessaSimps]

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

[MikeMillerGIS]

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.

[JohannesLindner]

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
    )

[VanessaSimps · Accepted solution]

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])