Return the count of a string in multiple fields using data expression

[dwold]

I am trying to build a bar chart illustrating the number of times a string is recorded across multiple (19) fields. I would like to get the total number of times Planning, Organization, Equipment, Training, Exercise, and Unknown appear in POETE area (poete_area) through POETE area 19 (poete_area_19).

This is what I have for my data expression so far but getting tripped up on it:

var portal = Portal('https://www.arcgis.com/');
var fs = FeatureSetByPortalItem(
    portal,
    '384158f2ebe34f8ba8b0b39dfe5f5af5',
    0,
    [
        'Jurisdiction',
        'core_capability',
        'target',
        'poete_area',
        'poete_area_2',
        'poete_area_3',
        'poete_area_4',
        'poete_area_5',
        'poete_area_6',
        'poete_area_7',
        'poete_area_8',
        'poete_area_9',
        'poete_area_10',
        'poete_area_11',
        'poete_area_12',
        'poete_area_13',
        'poete_area_14',
        'poete_area_15',
        'poete_area_16',
        'poete_area_17',
        'poete_area_18',
        'poete_area_19'
    ],
    false
);

function poeteCount(pa, type){
  Return IIf(pa==type, 1, 0)
}

var poeteType = ["Planning","Organization","Equipment","Training", "Exercise", "Unknown"];
var arrayLength = poeteType .length;
for (var i = 0; i < arrayLength; i++) {
console.log(poeteType [i]); 
}

for (var t in fs) {
// Console(IIf(t["poete_area"]=="Planning", 1, 0))

//Console(poeteCount(t["poete_area"],"Planning"))

var arrayLength = (poeteCount(t["poete_area"],"Planning")+poeteCount(t["poete_area_2"],"Planning"))
//Console(cntPlanning)
}

var poeteDict = {  
 fields: [
  { name: 'Jurisdiction', type: 'esriFieldTypeString' },  
  { name: 'core_capability',type: 'esriFieldTypeString'},
  { name: 'target',type: 'esriFieldTypeString'},  
  { name: 'poete_area',type: 'esriFieldTypeString'},
  { name: 'poete_area_2',type: 'esriFieldTypeString'},
  { name: 'poete_area_3',type: 'esriFieldTypeString'},
  { name: 'poete_area_4',type: 'esriFieldTypeString'},
  { name: 'poete_area_5',type: 'esriFieldTypeString'},
  { name: 'poete_area_6',type: 'esriFieldTypeString'},
  { name: 'poete_area_7',type: 'esriFieldTypeString'},
  { name: 'poete_area_8',type: 'esriFieldTypeString'},
  { name: 'poete_area_9',type: 'esriFieldTypeString'},
  { name: 'poete_area_10',type: 'esriFieldTypeString'},
  { name: 'poete_area_11',type: 'esriFieldTypeString'},
  { name: 'poete_area_12',type: 'esriFieldTypeString'},
  { name: 'poete_area_13',type: 'esriFieldTypeString'},
  { name: 'poete_area_14',type: 'esriFieldTypeString'},
  { name: 'poete_area_15',type: 'esriFieldTypeString'},
  { name: 'poete_area_16',type: 'esriFieldTypeString'},
  { name: 'poete_area_17',type: 'esriFieldTypeString'},
  { name: 'poete_area_18',type: 'esriFieldTypeString'},
  { name: 'poete_area_19',type: 'esriFieldTypeString'}
 ],  
 'geometryType': '',   
 'features': []
 };

 return poeteDict

Any tips would be greatly appreciated.

@jcarlson 

@JohannesLindner 

[JillianStanford]

Hi,

I haven't worked with it but would a combination of the Filter and GroupBy functions be helpful?

This sample has an example of the kind of output you're looking for - Aggregate by Day of Week 

[jcarlson · Accepted solution]

I bet we could get this with a single GroupBy. We'll use a couple other tricks to keep the expression from getting too long, though.

The crux of it is using a SQL expression to count across fields, like this:

CASE WHEN poete_area = 'Planning' THEN 1 ELSE 0 END +
CASE WHEN poete_area2 = 'Planning' THEN 1 ELSE 0 END +
...
CASE WHEN poete_aera19 = 'Planning' THEN 1 ELSE 0 END

It's a long expression, and you'd have to do it for every single option you want counted up, so the arrays and functions can take care of that for us.

// list of areas; we'll splice it into the field list later
var areas = [
  "poete_area",
  "poete_area_2",
  "poete_area_3",
  "poete_area_4",
  "poete_area_5",
  "poete_area_6",
  "poete_area_7",
  "poete_area_8",
  "poete_area_9",
  "poete_area_10",
  "poete_area_11",
  "poete_area_12",
  "poete_area_13",
  "poete_area_14",
  "poete_area_15",
  "poete_area_16",
  "poete_area_17",
  "poete_area_18",
  "poete_area_19"
];

var fs = FeatureSetByPortalItem(
  Portal("https://www.arcgis.com/"),
  "384158f2ebe34f8ba8b0b39dfe5f5af5",
  0,
  Splice(["Jurisdiction", "core_capability", "target"], areas),
  false
);

// values to sum
var values = [
  'Planning',
  'Organization',
  'Equipment',
  'Training',
  'Exercise',
  'Unknown'
]

// function for building SQL string
function poeteSQL(p) {
  
  // build array of SQL strings per area field
  var area_strings = [];

  for (var a in areas) {
    Push(
      area_strings,
      `CASE WHEN ${areas[a]} = '${p}' THEN 1 ELSE 0 END`
    )
  }

  // give back a single string that sums the areas
  return Concatenate(area_strings, ' + ')
}

// array of statistics
var stats = []

for (var v in values) {
  Push(
    stats,
    {
      name: `${values[v]}_count`,
      expression: poeteSQL(values[v]),
      statistic: 'SUM'
    }
  )
}

// group results into single row and sum
return GroupBy(
  fs,
  {name: 'id', expression: '1'},
  stats
)

[dwold]

@jcarlson live saver! Thank you, thank you, thank you!!