Removing blank values via Arcade Expression

404
5
Jump to solution
04-23-2021 12:05 PM
KieranClark
Occasional Contributor

Hi folks,

I've created a map layout that pulls data from various fields in our new database. Everything is connected and working great now and all the data is coming across mostly correct. The one remaining hurdle I have right now is that when trying to pull data for System Supplier, System Installer and Owner(s) it displays the correct company names but it also carries over a bunch of blank values.

Like so (outlined in red):

SEAMEWE3Companies.png

 My Arcade expression is as follows.

 

var role = $feature.ROLE_ID
If(role == 12){
return $feature.COMPANY_NAME
}

 

 The intent is to check if a company is ROLE_ID 12 (System Supplier in this case) and return the Company name if it is.

Where I'm stuck is I am not sure why it is even accounting for other companies that don't match the ROLE_ID and fill up my data fields with blanks.

I'm sure it's something simple I'm overlooking but I've been staring at this all day and could use a fresh pair of eyes...

0 Kudos
1 Solution

Accepted Solutions
jcarlson
MVP Regular Contributor

That appears to be a dynamic text layout element. What you're seeing is being caused by the delimiter on the dynamic text. By default, the delimiter includes a whitespace character in addition to whatever you include.

When you define a delimiter, it will return all the values in the table, even the nulls, separated by that delimeter:

jcarlson_0-1619205526373.png

 

Since you're using an Arcade expression, try this:

 

var role = Boolean($feature.ROLE_ID == 12);
if(role){
    return $feature.COMPANY_NAME + ', '
}

 

jcarlson_1-1619205687467.png

A bit better, but there's a pesky comma at the end of the string. Since your expression is simply filtering based on an attribute, it's simpler to add a Custom query to the dynamic text itself.

jcarlson_3-1619205837600.png

 

 

 

- Josh Carlson
Kendall County GIS

View solution in original post

5 Replies
jcarlson
MVP Regular Contributor

That appears to be a dynamic text layout element. What you're seeing is being caused by the delimiter on the dynamic text. By default, the delimiter includes a whitespace character in addition to whatever you include.

When you define a delimiter, it will return all the values in the table, even the nulls, separated by that delimeter:

jcarlson_0-1619205526373.png

 

Since you're using an Arcade expression, try this:

 

var role = Boolean($feature.ROLE_ID == 12);
if(role){
    return $feature.COMPANY_NAME + ', '
}

 

jcarlson_1-1619205687467.png

A bit better, but there's a pesky comma at the end of the string. Since your expression is simply filtering based on an attribute, it's simpler to add a Custom query to the dynamic text itself.

jcarlson_3-1619205837600.png

 

 

 

- Josh Carlson
Kendall County GIS

View solution in original post

KieranClark
Occasional Contributor

Wow, yes this is absolutely what I wanted. Thank you!

SEAMEWE3CompanyFixed.png

0 Kudos
KieranClark
Occasional Contributor

Hi Josh,

Sorry to bug you again but I have one last tweak I'm trying to figure out.

Some cable systems (mostly planned) do not have a System Installer available yet. What I would like to do is if there are no companies that match ROLE_ID of 11 (System Installer) then display "Not Available"

0 Kudos
jcarlson
MVP Regular Contributor

Oh, that's tricky. Since you're using a dynamic text element, I don't think there's a "no data" option to return a separate string when there are no values.

Since you're using Arcade per-feature, anything you add to that expression will end up repeating for each row, too, so we can't do it there.

Here's a possible workaround: use the function Distinct on the layer to return the distinct values for the field in question, and insert your "Not Available" string if the length of the returned array is 0. You'd need some way of then limiting the dynamic text element to show the first value in the table. I haven't tested it, but it's the only think I can think of that might work.

- Josh Carlson
Kendall County GIS
0 Kudos
KieranClark
Occasional Contributor

Hi Josh,

I'm trying to tackle this issue again after a few weeks of working on other things for this publication and I'm having a little trouble understanding how you're suggesting to use the Distinct function. I'm not sure how to set it up properly to even return a value of 0 if nothing matches.

0 Kudos