Create a data view for summarizing data filtered by group and date

Question

Hi All,

I have a fire report dataset where each fire has multiple reports associated with it. I have an ExB app where I'd like to show some summary indicators. However, I need to compute summaries from only the most recent reports for each fire. I don't see how to create a data view to get this information. I am feeling rather constrained with only being able to write SQL WHERE clauses to filter data and wish there was some way to include GROUP BY or MAX() to get the most recent report dates grouped by fire id. Can anyone help me think of a workaround for this?

Thanks in advance!

Anonymous User · Answer

Hi,

I can't offer a solution inside Experience Builder, but from the REST end point, you might be able to achieve these results. It would then be a matter of getting ExB to make the query you want. It's a matter of using the GROUPBY and the OutputStatistics. Note, you'll get back a JSON "Table", you dont get something back attached to your geometries. If you want to do GROUPBY and keep you geometry, you may want to look into using a QueryLayer (assuming your data source is your on-premise Enterprise using an enterprise geodatabase).

Example of the groupby/statistics:

http://sampleserver6.arcgisonline.com/arcgis/rest/services/ServiceRequest/FeatureServer/0/query?where=1%3D1&objectIds=&time=&geometry=&geometryType=esriGeometryEnvelope&inSR=&spatialRel=esriSpatialRelIntersects&distance=&units=esriSRUnit_Foot&relationParam=&outFields=*&returnGeometry=true&maxAllowableOffset=&geometryPrecision=&outSR=&having=&gdbVersion=&historicMoment=&returnDistinctValues=false&returnIdsOnly=false&returnCountOnly=false&returnExtentOnly=false&orderByFields=&groupByFieldsForStatistics=requesttype&outStatistics=%5B%7B%22statisticType%22%3A%22COUNT%22%2C%22onStatisticField%22%3A%22status%22%2C%22outStatisticFieldName%22%3A%22statusCnt%22%7D%5D&returnZ=false&returnM=false&multipatchOption=xyFootprint&resultOffset=&resultRecordCount=&returnTrueCurves=false&returnExceededLimitFeatures=false&quantizationParameters=&returnCentroid=false&sqlFormat=none&resultType=&featureEncoding=esriDefault&f=html

JonathanMcDougall · Answer

@KimberlyMcCallum

So, from a non-coder pov, I've found that creating data views & joins in the right order is helpful. Pick the right view, then when you connect data, the max and avg data elements are easier to tease out. I've also found that including a filter for users to pick returns is helpful too.

I've been creating returns for Covid-19 mobile testing centre returns - in my data I've created a view for a specific element then, in that view, ensured I've set the sort. Getting the max or avg etc is then very easy. For more complex data, I've started at the hosted feature service level and created a view layer, then from there started the filtering.

I realise it sounds like a faff, but I've also found that over the last few weeks I've been able to trim down some of the steps.

Realise this may not be quite the reply you're looking for, but may help with some ideas.