Table - Transpose dates into columns

SimonGIS · ‎02-06-2023

I have a public layer for Fire Danger Forecasts by district.

Each DIST_NAME has 4 records for the next 4 days
START_DATE is a date field. This is not constant, and is always today + 3 days for the forecasts
Each record has a FireDanger value which is the forecast. e.g. Moderate
There is a Forecast_Period which is 1-4. If I can't work out how to convert the date to the day name, this might be an easier one to work

I want to use an advanced expression to split the table out to

One District per row
Columns for each of the four days.
- dd/mm/yyyy or the actual day. (Monday, Tuesday, etc).
The Fire Danger Rating in each of these columns for the appropriate days

Final end goal looking to replicate:

I have been looking through the posts and samples, but yet to find anything that helps dimension a table in this way. Can someone give me some pointers or similar examples they have seen to get me started? New to Arcade.

I was thinking GroupBy might be the right approach? But the expression statistic type is geared more to working with numerical data.

I suspect I am missing something quite obvious here.

var fs = FeatureSetByPortalItem(Portal('https://arcgis.com/'), 'b8505ee34f414a5a842b0f0a32ea3e94', 5, ['DIST_NAME', 'FireDanger', 'Start_Time', 'FireBehavIndex'], false);

var features = [];

for (feature in fs) { 
    var feat = {'attributes': { 
            'District': '',
            'Sunday': '',
            'Monday': '',
            'Tuesday': '',
            'Wednesday': ''
        }}
    feat.attributes.District = feature['DIST_NAME'];
    feat.attributes[Text(ToUTC(feature['Start_Time']), 'dddd')] = feature['FireDanger'] + ', ' + feature['FireBehavIndex'];
    Push(features, feat);
};

var fs_dict = {
    'fields': [
        { 'name': 'District', 'type': 'esriFieldTypeString'},
        { 'name': 'Sunday', 'type': 'esriFieldTypeString'},
        { 'name': 'Monday', 'type': 'esriFieldTypeString'},
        { 'name': 'Tuesday', 'type': 'esriFieldTypeString'},
        { 'name': 'Wednesday', 'type': 'esriFieldTypeString'},
    ], 
    'geometryType': '', 
    'features': features
};


var fsDict = FeatureSet(Text(fs_dict)); 

var fs_gp = GroupBy(fsDict, ['District'], 
[{name: 'Sunday', expression: "Sunday", statistic: 'sum' }, 
 {name: 'Monday', expression: 'Monday', statistic: 'sum' },
 {name: 'Tuesday', expression: 'Tuesday', statistic: 'sum' },
 {name: 'Wednesday', expression: 'Wednesday', statistic: 'sum' },
])

return fs_gp

Returns: