Data Expression in Dashboard

265
7
a month ago
Labels (2)
JanelleShank
New Contributor II

Hello All! 

I am looking to write a data expression that takes an attribute field from the hosted table and creates a new field with a new assigned value based on the value from the field I am pulling from. However, I cannot seem to get this expression to run correctly. Any help and corrections to get this expression to run is very much appreciated. Thank you! Below is my code:

var portal = Portal('https://.maps.arcgis.com')
var fs = FeatureSetByPortalItem(
    portal,
    '155af3e091404240acd2904e34a07bf6',
    0,
    ['Contest_title'],
    false
);
 
// Create empty array for features and feat object
var features = [];
var feat;
 
// Split comma separated hazard types and store in dictionary.  
for (var feature in fs) { 
if ($feature["Contest_title"] == "President of the United States (REP)" ) {return "A"}
if ($feature["Contest_title"] == "United States Senator (REP)" ) {return "C"}
if ($feature["Contest_title"] == "Attorney General (REP)" ) {return "E"}
if ($feature["Contest_title"] == "Auditor General (REP)" ) {return "G"}
if ($feature["Contest_title"] == "State Treasurer (REP)" ) {return "I"}
if ($feature["Contest_title"] == "Representative in Congress -13th District (REP)" ) {return "K"}
if ($feature["Contest_title"] == "Representative in the General Assembly -78th District (REP)" ) {return "O"}
if ($feature["Contest_title"] == "Delegate to the Republican National Convention (REP)" ) {return "M"}
if ($feature["Contest_title"] == "Alternate Delegate to the Republican National Convention (REP)" ) {return "P"}
if ($feature["Contest_title"] == "Republican Committee Person - Bedford Borough West (REP)" ) {return "Q"}
if ($feature["Contest_title"] == "Republican Committee Person - Bedford Township 1 (REP)" ) {return "R"}
if ($feature["Contest_title"] == "Republican Committee Person - Bedford Township 2 (REP)" ) {return "S"}
if ($feature["Contest_title"] == "Republican Committee Person - Everett Borough (REP)" ) {return "T"}
if ($feature["Contest_title"] == "Republican Committee Person - Hopewell Township (REP)" ) {return "U"}
if ($feature["Contest_title"] == "Republican Committee Person - King Township (REP)" ) {return "V"}
if ($feature["Contest_title"] == "Republican Committee Person - West Providence Township (REP)" ) {return "W"}
if ($feature["Contest_title"] == "Republican Committee Person - West St. Clair Township (REP)" ) {return "X"}
if ($feature["Contest_title"] == "Republican Committee Person - Woodbury Township (REP)" ) {return "Y"}
if ($feature["Contest_title"] == "President of the United States (DEM)" ) {return "B"}
if ($feature["Contest_title"] == "United States Senator (DEM)" ) {return "D"}
if ($feature["Contest_title"] == "Attorney General (DEM)" ) {return "F"}
if ($feature["Contest_title"] == "Auditor General (DEM)" ) {return "H"}
if ($feature["Contest_title"] == "State Treasurer (DEM)" ) {return "J"}
if ($feature["Contest_title"] == "Representative in Congress -13th District (DEM)" ) {return "L"}
if ($feature["Contest_title"] == "Delegate to the Democratic National Convention (DEM)" ) {return "N"}
else {return "Unsatisfactory"}
}
            Push(features, feat);
}}
 
// Empty dictionary to capture each hazard reported as separate rows. 
var choicesDict = {
    'fields': [
        { 'name': 'Contest_ID', 'type': 'esriFieldTypeString'}], 
    'geometryType': '',
    'features': features
}; 
 
// Convert dictionary to featureSet. 
var fs_dict = FeatureSet(Text(choicesDict)); 
 
// Return featureset after grouping by hazard types. 
return GroupBy(fs_dict, ['Contest_ID'], 
       [{ name: 'Contest_ID', expression: 'Contest_ID', statistic: 'MAX' }]);  
0 Kudos
7 Replies
KenBuja
MVP Esteemed Contributor

With a "return" in each of the if statements, the code will short-circuit and return a single letter (or "Unsatisfactory") for the first feature.

You'll want to use the Decode function to return a variable for that logic to be used later. Here, I'm just making a guess in this code without seeing your data and what you're trying to accomplish. It's also untested, so it might need modification.

var portal = Portal('https://.maps.arcgis.com')
var fs = FeatureSetByPortalItem(
    portal,
    '155af3e091404240acd2904e34a07bf6',
    0,
    ['Contest_title'],
    false
);

// Create empty array for features and feat object
var features = [];
var feat;

for (var f in fs) {
  var contest = Decode(f["Contest_title"],"President of the United States (REP)","A",
                                          "United States Senator (REP)", "C",
                                          "Attorney General (REP)", "E",
                                          //the rest of the options
                                          "Delegate to the Democratic National Convention (DEM)", "N",
                                          "Unsatisfactory");
  feat = {
    attributes: {
      Contest_ID: contest,
    },
  };
  Push(features, feat);
}
var choicesDict = {
    'fields': [
        { 'name': 'Contest_ID', 'type': 'esriFieldTypeString'}], 
    'geometryType': '',
    'features': features
}; 
 
// Convert dictionary to featureSet. 
var fs_dict = FeatureSet(Text(choicesDict)); 
 
// Return featureset after grouping by hazard types. 
return GroupBy(fs_dict, ['Contest_ID'], 
       [{ name: 'Contest_ID', expression: 'Contest_ID', statistic: 'MAX' }]);  

0 Kudos
JanelleShank
New Contributor II

Thank you for the help. 

I ran the code and tried to modify a little bit but is still throwing an error at the line with the decode function. Below is a snapshot of the data I am using. My ultimate goal for this is be able to write a data expression to create a new field that assigns each contest title a letter. That way I can sort those contest titles in specific order in the category selector on the Dashboard. 

JanelleShank_0-1713900977061.png

 

0 Kudos
KenBuja
MVP Esteemed Contributor

Can you post your new code?

0 Kudos
JanelleShank
New Contributor II

Sure! Below is the new code. 

var fs =  FeatureSetByPortalItem(
    Portal("https://.maps.arcgis.com"),
    // portal item id
    "178eb292ce436450b85dc249bd3069741",
    0,
    ['Contest_title'],
    false
);
 
// Create empty array for features and feat object
var features = [];
var feat;
 
for (var f in fs) {
  var contest = Decode(f["Contest_title"],"President of the United States (REP)","A",
                                          "United States Senator (REP)", "C",
                                          "Attorney General (REP)", "E",
  "Auditor General (REP)", "G",
  "State Treasurer (REP)", "I",
  "Representative in Congress -13th District (REP)", "K",
  "Representative in the General Assembly -78th District (REP)", "O"
                                          "Delegate to the Republican National Convention (REP)" , "M",
                                          "Alternate Delegate to the Republican National Convention (REP)", "P",
                                          "Republican Committee Person - Bedford Borough West (REP)", "Q",
                                          "Republican Committee Person - Bedford Township 1 (REP)", "R",
                                          "Republican Committee Person - Bedford Township 2 (REP)", "S",
                                          "Republican Committee Person - Everett Borough (REP)", "T",
                                          "Republican Committee Person - Hopewell Township (REP)", "U",
            "Republican Committee Person - King Township (REP)", "V",
            "Republican Committee Person - West Providence Township (REP)", "W",
    "Republican Committee Person - West St. Clair Township (REP)", "X",
    "Republican Committee Person - Woodbury Township (REP)", "Y",
    "President of the United States (DEM)", "B",
    "United States Senator (DEM)", "D",
    "Attorney General (DEM)", "F",
            "Auditor General (DEM)", "H",
            "State Treasurer (DEM)", return "J",
            "Representative in Congress -13th District (DEM)", "L"
                                          "Delegate to the Democratic National Convention (DEM)", "N",
                                          "Unsatisfactory");
  feat = {
    attributes: {
      Contest_ID: contest,
    },
  };
  Push(features, feat);
}
var choicesDict = {
    'fields': [
        { 'name': 'Contest_ID', 'type': 'esriFieldTypeString'}], 
    'geometryType': '',
    'features': features
}; 
 
// Convert dictionary to featureSet. 
var fs_dict = FeatureSet(Text(choicesDict)); 
 
// Return featureset after grouping by hazard types. 
return GroupBy(fs_dict, ['Contest_ID'], 
       [{ name: 'Contest_ID', expression: 'Contest_ID', statistic: 'MAX' }]); 
0 Kudos
KenBuja
MVP Esteemed Contributor

You had a couple of mistakes (missing commas and an errant return)

  var contest = Decode(f["Contest_title"],"President of the United States (REP)","A",
                                          "United States Senator (REP)", "C",
                                          "Attorney General (REP)", "E",
                                          "Auditor General (REP)", "G",
                                          "State Treasurer (REP)", "I",
                                          "Representative in Congress -13th District (REP)", "K",
                                          "Representative in the General Assembly -78th District (REP)", "O",
                                          "Delegate to the Republican National Convention (REP)" , "M",
                                          "Alternate Delegate to the Republican National Convention (REP)", "P",
                                          "Republican Committee Person - Bedford Borough West (REP)", "Q",
                                          "Republican Committee Person - Bedford Township 1 (REP)", "R",
                                          "Republican Committee Person - Bedford Township 2 (REP)", "S",
                                          "Republican Committee Person - Everett Borough (REP)", "T",
                                          "Republican Committee Person - Hopewell Township (REP)", "U",
                                          "Republican Committee Person - King Township (REP)", "V",
                                          "Republican Committee Person - West Providence Township (REP)", "W",
                                          "Republican Committee Person - West St. Clair Township (REP)", "X",
                                          "Republican Committee Person - Woodbury Township (REP)", "Y",
                                          "President of the United States (DEM)", "B",
                                          "United States Senator (DEM)", "D",
                                          "Attorney General (DEM)", "F",
                                          "Auditor General (DEM)", "H",
                                          "State Treasurer (DEM)", "J",
                                          "Representative in Congress -13th District (DEM)", "L",
                                          "Delegate to the Democratic National Convention (DEM)", "N",
                                          "Unsatisfactory");
0 Kudos
JanelleShank
New Contributor II

Thank you for catching those errors. However, now what I run it. These are the results. 

JanelleShank_0-1713908164528.png

Is there something I'm missing at the end of my code?

0 Kudos
jcarlson
MVP Esteemed Contributor

GroupBy is meant to give you numeric statistics, but your Contest_ID field is a string. Also your grouping field and the calculated field are the same name. Try changing the field name for the "max" to something else.

It's not really clear to me what this data expression is for. You're replacing specific strings with letter codes, but then you're grouping them. What is the grouping for? Are you trying to find the number of occurrences of each? You'd need COUNT instead of MAX.

Also, if you're just recoding your strings, you can get rid of the entire intermediate featureset completely. GroupBy allows you to use a SQL expression for the grouping field, too! And you can use SQL wildcards with LIKE to shorten up your conditions, too.

Getting rid of a for-loop and using a SQL-based function like GroupBy will improve your expression's execution time, too.

var fs =  FeatureSetByPortalItem(
    Portal("https://.maps.arcgis.com"),
    // portal item id
    "178eb292ce436450b85dc249bd3069741",
    0,
    ['Contest_title'],
    false
);

var sql = `CASE
WHEN Contest_title LIKE 'President%REP%' THEN 'A'
WHEN Contest_title LIKE 'President%DEM%' THEN 'B'
WHEN Contest_title LIKE 'Attorney%REP%' THEN 'C'
-- and so on --
ELSE 'Unsatisfactory'
END`

return GroupBy(
  fs,
  {name: 'Contest_ID', expression: sql},
  {name: 'Contest_ID_Count', expression: '1', statistic: 'COUNT'}
)

 

- Josh Carlson
Kendall County GIS
0 Kudos