Counting rows with a null values within an Indicator

Question

I am trying to configure a Dashboard Indicator to total the number of rows in a table that has at least one null value. If there is one cell in a row with a null value (or many cells with a null value), we consider that entry an "unreported" entry. The goal of the indicator is to show how many unreported entries are in the table, filter those values, find who is not reporting correctly, etc.

Doing it by row is ideal since a complete row = a complete entry, but if we have to work around it, we count how many null values are within a column.

I am still new to Arcade so any help is appreciated.

Thanks in advance!!!

JustinH · Answer

Thank you all for the help on this....

I found that within an indicator I can filter by which features are null and the indicator will count those features. No arcade needed. I think I did not see an operator "is null" before. I will certainly use some of this code if needed in the future. Thank you so much for the input!!!

jcarlson · Answer

I like a good GroupBy statement in a Data Expression, as it evaluates a lot faster than feature by feature. We also want this information to be available for filtering other widgets in your dashboard, so we need to keep the objectid or globalid in the output. Inside of the GroupBy expression, we can also use some basic SQL. In this case, we use "CASE WHEN THEN ELSE END". It could be a long expression, but essentially, anything null counts as 1, non-nulls as 0, and the final value will be the number of null fields. Anything above 0 meets your criteria for "incomplete". The output of this expression will have one row for every feature in the layer, and you should be able to use it to filter other widgets accordingly. If you'd rather just have an expression that evaluates to "complete" or "incomplete", that's also possible with some adjustments to the SQL. var fs = FeatureSetByPortalItem( Portal('your portal url'), 'itemid of the layer', 0, // layer index ['objectid'], // since we are using GroupBy, we don't need any fields in the FS false ) var sql = ` CASE WHEN first_field IS NULL THEN 1 ELSE 0 END + CASE WHEN second_field IS NULL THEN 1 ELSE 0 END + CASE WHEN third_field IS NULL THEN 1 ELSE 0 END ` // group by objectid to keep every feature separate in the output return GroupBy( fs, 'objectid', { name: 'null_fields', expression: sql, statistic: 'SUM' } )

kenbuja · Answer

One way to do that is to get the Schema of your FeatureSet, which gets you a list of all the fields. You can loop through the FeatureSet and check the value for each field to see if it has a null. If you have a smaller list of fields, you can use that instead. If one of the values is null, the counter increases by one, the inner loop breaks, and the outer loop goes to the next record.

This is the data expression for the indicator

var incompleteRows = 0;
var fs = FeatureSetByPortalItem(
  Portal("your portal"),
  // portal item id
  "your item",
  0, // layer id
);

//use this if you have specific fields to check
Expects(fs, 'field1', 'field2', 'field3')
var fields = ['field1', 'field2', 'field3']
for (var feat in fs) {
  for (var field in fields) {
    if (IsEmpty(feat[fields[field]])) {
      incompleteRows++;
      break;
    }
  }
}

//use this if you want to check all fields
Expects(fs, '*')
var theSchema = Schema(fs)
for (var feat in fs) {
  for (var field in theSchema.fields) {
    if (IsEmpty(feat[theSchema.fields[field].name])) {
      incompleteRows++;
      break;
    }
  }
}

//return the FeatureSet for the indicator
var Dict = {  
 'fields': [  
 {'name': 'IncompleteRows','type': 'esriFieldTypeInteger'}],  
 'geometryType': '',   
 'features': [{
  'attributes': {
    'IncompleteRows': incompleteRows
  }
 }]};

return FeatureSet(Dict);