Community
Select to view content in your preferred language

Parse Duplicate days in a count in a Table

609
10
10-08-2025 10:26 AM
ScottJones1
by
Frequent Contributor
‎10-08-2025 10:26 AM
I have grouped table, The Incidents might get multiple survey entries a day from different people. I just want to show how many days we have been on an incident. Is there a way to parse out duplicate days?
 

return {
  cells: {
    Incident_: {
      displayText : $datapoint.Incident_,
      textColor: '',
      backgroundColor: '',
      textAlign: 'left',
      iconName: '',
      iconAlign: '',
      iconColor: '',
      iconOutlineColor: ''
    },
   
    COUNT_DATE_OF_EVENT: {
      displayText : Text($datapoint.COUNT_DATE_OF_EVENT),
      textColor: '',
      backgroundColor: '',
      textAlign: 'right',
      iconName: '',
      iconAlign: '',
      iconColor: '',
      iconOutlineColor: ''
    }
  }
}
0 Kudos
10 Replies
Neal_t_k
by
Honored Contributor
‎10-13-2025 02:20 PM

@ScottJones1 Well shoot, but makes sense, if there is 1 entry 1-1= 0.  And didn't think of non-consecutive days. 

Haven't tested this yet, but might be better.  I kept the "duration" in there for comparison.

var portal = Portal('https://www.arcgis.com');
var layer = FeatureSetByPortalItem(portal, '77fb11f20f7f4b689d38258d3b5b0f43', 0, ['date_of_event', 'Incident_']);

var groups = Distinct(layer, ['Incident_']);
var features = [];

for (var g in groups) {
    var groupValue = g['Incident_'];
    if (IsEmpty(groupValue)) continue;

    var filtered = Filter(layer, 'Incident_ = @groupValue');

    var uniqueDates = Distinct(filtered, ['date_of_event']);
    var dateCount = Count(uniqueDates);

    var start = First(Top(OrderBy(filtered, 'date_of_event ASC'), 1));
    var end = First(Top(OrderBy(filtered, 'date_of_event DESC'), 1));


    var duration = DateDiff(end['date_of_event'], start['date_of_event'], 'days') + 1;

    Push(features, {
        attributes: {
            Group: groupValue,
            StartDate: start['date_of_event'],
            EndDate: end['date_of_event'],
            Duration: duration,
            UniqueDays: dateCount
        }
    });
}

return FeatureSet(Text({
    fields: [
        { name: "Group", type: "esriFieldTypeString" },
        { name: "StartDate", type: "esriFieldTypeDate" },
        { name: "EndDate", type: "esriFieldTypeDate" },
        { name: "Duration", type: "esriFieldTypeInteger" },
        { name: "UniqueDays", type: "esriFieldTypeInteger" }
    ],
    geometryType: "",
    features: features
}));

Edit: Had some time to test and this should work if your data field doesn't have time included, which I think yours doesn't...But if it does, your have to strip the time from the dates before counting unique days. so replace this:

    var uniqueDates = Distinct(filtered, ['date_of_event']);
    var dateCount = Count(uniqueDates);

with this:

    var dateStrings = [];
    for (var f in filtered) {
        var rawDate = f.date_of_event;
        if (!IsEmpty(rawDate)) {
            var dateStr = Text(rawDate, 'yyyy-MM-dd');
            Push(dateStrings, dateStr);
        }
    }

    var uniqueDateStrings = Distinct(dateStrings);
    var dateCount = Count(uniqueDateStrings);

 

0 Kudos