topic Re: Arcade Script to join table to point Layer for pie chart ERROR in ArcGIS Dashboards Questions

Arcade Script to join table to point Layer for pie chart ERROR

ALICEANSTEE — Thu, 04 Jul 2024 14:04:52 GMT

Hi. I am currently trying to join a table to a point hosted layer to display additional fields in a pie chart in dashboard. My current script ( I used a little of it from GitHub) doesnt show any errors in the actual script but comes up with this error :

Expected "!=", "(", "*", "+", "-", "/", "<", "<=", "<>", "=", ">", ">=", "AND", "OR", [ \t\n\r], or end of input but "5" found.

Does anyone know where the errors lies? I can't locate any wrong syntax so thinking it may be another issue? My script is below (pointfs is the feature set and tablefs is the table I want to join to - based on a common attribute that holds Postcodes (PostcodeSector)) :

(I've removed the portal link at the top for privacy).

var portal_Link = Portal('xyz');
var pointfs = FeatureSetByPortalItem(portal_Link,'17d25668c1ee4904ac2920558175f291',0,["*"], false);
var tablefs = FeatureSetByPortalItem(portal_Link,'76a135fad8db4da7bc2932ec7c58dd5b',0,["*"], false);

var features = [];
var feat;

for (var t in tablefs){
var tableID = t["user_postsector"]
for (var p in Filter(pointfs, "Postcode_Sector= "+tableID)){
feat = {
attributes: {
organisation: tableID,
PostcodeSectorAv: p["user_postcodesectoraverage"],
PostcodeSectorCount: p["user_postsectorcountvalued"],
GSS_CODE: t["GSS_CODE"]
}
}
Push(features, feat)
}
}

var joinedDict = {
field: [
{name: "organisation", type: "esriFieldTypeString"},
{name: "PostcodeSectorAv,", type: "esriFieldTypeInteger"},
{name: "PostcodeSectorCount", type: "esriFieldTypeInteger"},
{name: "GSS_CODE", type: "esriFieldTypeString"},
],
'geometryType': '',
'features':features
};

return FeatureSet(Text(JoinedDict));

Re: Arcade Script to join table to point Layer for pie chart ERROR

jcarlson — Thu, 04 Jul 2024 14:46:11 GMT

That error comes from a SQL statement, which in this expression, is your Filter function.

Generally, functions that use a SQL expression with a dynamic value, like your table ID, should reference the value in one of two ways.

One is to establish the variable, then use @Variable80_name in the SQL string:

var tableID = t['user_postsector'] for (var p in Filter(pointfs, "Postcode_Sector=@tableID")) { … }

The other is to use a template literal. I particularly like this approach myself, as you don't need to create a variable just for one thing.

for (var p in Filter(pointfs, `Postcode_Sector=${t['user_postsector']}`)) { … }

See if either of those help.

Re: Arcade Script to join table to point Layer for pie chart ERROR

ALICEANSTEE — Fri, 05 Jul 2024 08:54:39 GMT

Hi.

Thanks thats helped move past the SQL error - appreciate the help. I am now getting this error:

Execution error - Line : 44, 7: Invalid parameter

I've tried to get distinct values in a new subset of the house price estimations per postcode sector to try and make the script quicker - I'm not sure if this is then causing an error or if its because the output is a dictionary? I get the same output when I end the script with : return FeatureSet(Text(joinedDict))

Any additional help would be great!

var portal_Link = Portal('xyzx');
var pointfs = FeatureSetByPortalItem(portal_Link,'17d25668c1ee4904ac2920558175f291',0,["*"], false);
var tablefs = FeatureSetByPortalItem(portal_Link,'76a135fad8db4da7bc2932ec7c58dd5b',0,["*"], false);

var fields = ["user_postsector", "user_postcodesectoraverage", "user_postsectorcountvalued"];
var DistinctTablefs = Distinct(tablefs, fields)

var features = [];
var feat;

for (var t in DistinctTablefs){
var tableID = t["user_postsector"]
for (var p in Filter(pointfs, "organisation=@tableID")){
feat = {
attributes: {
organisation: tableID,
PostcodeSectorAv: t["user_postcodesectoraverage"],
PostcodeSectorCount: t["user_postsectorcountvalued"],
GSS_CODE: p["GSS_CODE"]
}
}
Push(features, feat)
}
}

return FeatureSet(joinedDict);

Re: Arcade Script to join table to point Layer for pie chart ERROR

ALICEANSTEE — Fri, 05 Jul 2024 10:37:33 GMT

Update as of 05/07/2024. I've check the return of the features just before the creation of the var joinedDict and it returns the multiple dictionaries with a correct join (fab!). I've now been trying to merge the dictionaries together to create one.

This is one of the individual dictionaries:

any ideas on how best to do this? I assumed iterating through each would be best?

Thanks!

Re: Arcade Script to join table to point Layer for pie chart ERROR

jcarlson — Fri, 05 Jul 2024 13:41:23 GMT

In your joinedDict object, you have a key "field". That should be "fields".

I can't tell from your post which line of your code is line 44, so I don't know what parameter your expression might be referencing.

Re: Arcade Script to join table to point Layer for pie chart ERROR

ALICEANSTEE — Fri, 05 Jul 2024 13:59:01 GMT

Hi,

thanks for responding. Line 40 is the last line 'return FeatureSet(joinedDict);'

Thanks

Alice