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!

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'),
    'xyz',
    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: