How to combine, replace (or decode), and group values in a data expression?

[natasha]

Hello! I am working on a map and dashboard that uses a service from my city with a dataset on people involved in traffic incidents. Each row is an incident and I'd like to show in a list the number of incidents by TYPE in each WARD.

One of the fields is WARD, which is straightforward enough as it lists the ward in which each incident occured. Another field is TYPE which represents the type of vehicle/involvement.

However TYPE is represented by numbers like 03, 04, 05, 06, 18, 19, 21, ... where multiple numbers represent one type of vehicle/involvement (Pedestrian, Cyclist, Motorist.) The same numbers always represent the same modes of travel, and I am trying to figure out how to write a data expression that will enable me to replace/decode the values for each TYPE with the text form of the type of involvement, and then group to get the count for each involvement by ward. Or those steps in reverse, if that's more logical.

In the map, I could write an Arcade expression to configure the pop-up and symbology easily according to what each number in TYPE represents qualitatively. But I am new to dashboards, so trying to figure out how to bring this into the dashboard as a Data Expression so that I can configure it for the user to understand.

Thanks for your help!

[AdrianWelsh]

Natasha,

How are you trying to represent this on the dashboard? Are you wanting to utilize one of the elements to show these data (like a pie chart, bar graph, number, etc.)? If so, which element and what do you want it to show exactly?

[natasha]

Hi Adrian!

Yes, I would like to use this in an element, perhaps as a list that would show the name of each ward and the count of each type of incident by ward.

[AdrianWelsh]

I am not quite sure that this is possible with a simple list (as far as I know). I have done something similar but it took quite a bit of manual entry. In my dashboard we're looking at parcels of land and where each one is in the ROW (right of way) progression. So each parcel has a "stage" and each stage has a couple of "statuses". This is what I came up with using a combination of an Indicator for the count of each Stage and a Serial Chart to show the count of each Status within that stage:

The "issue" is that the data has to line up properly in the table in order to make this work correctly and I had to manually put in each type of "Stage" indicator. Which isn't bad when it's only 5 stages but still. Maybe something similar to this could be done?

[natasha]

Thanks for your reply. That looks cool and might be something I could work towards. I'm not set on a particular element yet, mostly concerned at this point with the data expression to get the feature set I'd need to combine values in a field and replace them with a string instead of number. Sort of like what these questions are asking:

Combine multiple choices into one named choice - Esri Community

Re: Replace Text in field and calculate statistics... - Esri Community

[AdrianWelsh]

I like those threads and where they're going! I hope it works out with the deep dive into Arcade (that begins to get a little over my head when it's beyond a few lines!).

[natasha]

Hehe same here!

[jcarlson]

Hi @natasha , I must have missed this when you originally posted it, but I think it's doable. We just want to group the data by two fields, but do some reshaping of one of the fields first. As in the other post where you pinged me, I do think that we can do it with a bit of SQL within a GroupBy.

In this case ("case", get it?) we will use the SQL "IN". By checking if our TYPE field is in a defined list, we can have it spit one value for any match.

I think it's a lot easier to make sense of it all if we format our SQL statement more nicely. Thankfully, I recently learned that while Arcade doesn't like multiline strings with quotes, it will respect them if you use backticks.

Here's the GroupBy version:

var fs = FeatureSetByPortalItem(
    Portal('https://www.arcgis.com'),
    'xxxxx',
    0,
    ['*'],
    false
);

var groupSQL = `CASE
    WHEN TYPE IN('01', '02', '03') THEN 'Bicycles'
    WHEN TYPE IN('03', '07', '13') THEN 'Scooters'
    ... etc
    ELSE 'Default value'
END`

var grouped = GroupBy(
    fs,
    [
        {name: 'vehicle_type', expression: groupSQL},
        {name: 'ward', expression: 'WARD'}
    ],
    {name: 'Cases', expression: 'cases', statistic: 'SUM'}
)

return grouped

 It's hard to demo without access to the data, but here's a silly example from the Arcade Playground, in which I use SQL to reclassify the sample buildings layer by floor count, and also split the result by whether the objectid is even or odd:

var fs = Filter($layer, "OBJECTID < 100")

var floors_sql = `CASE
    WHEN FLOORCOUNT = 1 THEN 'Single Story'
    WHEN FLOORCOUNT IN(2, 3) THEN 'A bit taller'
    WHEN FLOORCOUNT IN(4, 5, 6) THEN 'A tall building'
    WHEN FLOORCOUNT > 6 THEN 'A VERY tall building'
    ELSE 'No Floors?'
END`

var evenodd_sql = `CASE
    WHEN MOD(OBJECTID, 2) = 0 THEN 'EVEN'
    ELSE 'ODD'
END`

GroupBy(
    fs,
    [
        {name: 'floors_reclassed', expression: floors_sql},
        {name: 'objectid_evenodd', expression: evenodd_sql}],
    {name:'sum', expression:'1', statistic:'SUM'}
)

Which returns: