Select to view content in your preferred language

Table - Transpose dates into columns

646
0
02-06-2023 03:28 AM
SimonGIS
Regular Contributor

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

SimonGIS_0-1675682091802.png

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:

SimonGIS_1-1675682307766.png

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:

SimonGIS_0-1675857024085.png

 

 

Tags (1)
0 Kudos
0 Replies