topic Re: Calculate/concatenate depending on field values in Attribute Rules Questions

Calculate/concatenate depending on field values

VanessaSimps — Wed, 18 Jan 2023 00:40:14 GMT

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!

Re: Calculate/concatenate depending on field values

DavidSolari — Wed, 18 Jan 2023 00:44:48 GMT

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

Re: Calculate/concatenate depending on field values

JohannesLindner — Wed, 18 Jan 2023 07:12:53 GMT

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

Re: Calculate/concatenate depending on field values

VanessaSimps — Wed, 18 Jan 2023 17:34:19 GMT

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?

Re: Calculate/concatenate depending on field values

MikeMillerGIS — Wed, 18 Jan 2023 18:47:19 GMT

Could Prefix be None? Count errors on that

Re: Calculate/concatenate depending on field values

JohannesLindner — Wed, 18 Jan 2023 18:57:42 GMT

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 )

Re: Calculate/concatenate depending on field values

VanessaSimps — Thu, 19 Jan 2023 15:37:23 GMT

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

thanks!

Re: Calculate/concatenate depending on field values

VanessaSimps — Thu, 19 Jan 2023 15:46:20 GMT

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

Re: Calculate/concatenate depending on field values

VanessaSimps — Thu, 19 Jan 2023 15:47:20 GMT

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

Re: Calculate/concatenate depending on field values

MikeMillerGIS — Thu, 19 Jan 2023 15:49:41 GMT

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.

Re: Calculate/concatenate depending on field values

VanessaSimps — Tue, 04 Apr 2023 21:58:31 GMT

@DonSjoboen see if this give you any hints!