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));
Solved! Go to Solution.
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.
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 @Variable_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.
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)
}
}
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(joinedDict);
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!
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.
Hi,
thanks for responding. Line 40 is the last line 'return FeatureSet(joinedDict);'
Thanks
Alice