Using Data Expressions to make a distinct list

[KristineHubenig]

Hello!

I am new to Arcade and Data Expressions, I have been working on my expression and   testing ideas but  I can't seem to get anything to work. I'll explain what I am looking to do and an example of my script below.

I have a database with tons of records per person for different activities and I would like to make a list of the total counts of each activity per person.  I have in one field "PName" which contains the persons name, then another field "Group_Type" which contains 7 different groups, such as "Large Game", "Small Game", "Birds", "Fish", "Overnight Structures", "Plant and Earth Materials" and "Cultural Sites". I would like to use the data expressions to create a list in my dashboard for each person which contains the totals for each activity. So I could see something like:

Tim:

Fish: 23

Small Game: 4

Large Game: 7

and so on 

At first I just used groupby and only got the overall total per person, without the individual groupings. So then I tried to d a groupby and a distinct but I could not format it correctly to  see if it would work. Which leads me to where I currently am which is using filters. I have created multiple filters for each of the 7 categories and then groupby for each one. I figured the logic in my head worked but I am not sure about the actual arcade logic behind it, because I also cannot get it to run.  Yesterday I was getting errors for "Invalid Token" and "Invalid Parameter". Today when I go to test it out I am getting "g.charAt is not a function". But I do not believe I am using g.charAt anywhere in my script or what it is. If anyone can point me in the right direction that would be great! Or to tell me its not possible to do, then I can stop worrying about it!

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

var fish = Filter(fs, ["Group_Type"] == 'Fish');
var largegame = Filter(fs,["Group_Type"] == 'Large Game');
var smallgame = Filter(fs,["Group_Type"] == 'Small Game');
var bird = Filter(fs, ["Group_Type"] == 'Bird' );
var culturalmat =Filter(fs, ["Group_Type"] == 'Cultural Sites');
var earthworks = Filter(fs, ["Group_Type"] == 'Plant and Earth Materials');
var overnight = Filter(fs, ["Group_Type"] == 'Overnight Structures');

return Groupby(fs,['PName'],
[{name:'Total', expression: 'Group_Type',
statistic: 'COUNT'},
{name:'Fish', expression: fish, statistic: 'COUNT'},
{name:'LargeGame', expression: largegame, statistic: 'COUNT'},
{name:'SmallGame', expression: smallgame, statistic: 'COUNT'},
{name:'Bird', expression: bird, statistic: 'COUNT'},
{name:'CulturalSites', expression: culturalmat, statistic: 'COUNT'},
{name:'PlantandEarthMaterials', expression: earthworks, statistic: 'COUNT'},
{name:'OvernightStructures', expression: overnight, statistic: 'COUNT'}
]
);

[jcarlson · Accepted solution]

I'm happy to tell you that the solution should be very simple indeed. What you're describing can be taken care of in a single GroupBy function, if written correctly.

I believe that what you're seeing with that error is due to the Filter function being written improperly. The filter statement has to be single string, so instead of ["Group_Type"]=='Bird', you'd want something like "Group_Type = 'Bird'". This is because it's submitting a SQL query from your string.

It could also be that you're using your featuresets in the GroupBy function at the end. But I don't think we need to filter anything, honestly, and can bypass those errors entirely.

GroupBy can also take SQL expressions to define the fields you get in your output. Rather than count the number of features in a filtered featureset, consider this:

{name:'LargeGame', expression: "CASE WHEN Group_Type = 'Large Game' THEN 1 ELSE 0", statistic: 'SUM'}

When used in the function, this will add a 1 for every row with the matching Group_Type value of 'Large Game'. The result? The same thing as the count of a filtered set. The huge difference is that by putting it into a SQL expression, the work is all done by the server, and your expression will evaluate much more quickly.

So, to write your full expression out in this manner, it would look like this:

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


var fish =        "CASE WHEN Group_Type = 'Fish'                      THEN 1 ELSE 0 END"
var largegame =   "CASE WHEN Group_Type = 'Large Game'                THEN 1 ELSE 0 END"
var smallgame =   "CASE WHEN Group_Type = 'Small Game'                THEN 1 ELSE 0 END"
var bird =        "CASE WHEN Group_Type = 'Bird'                      THEN 1 ELSE 0 END"
var culturalmat = "CASE WHEN Group_Type = 'Cultural Sites'            THEN 1 ELSE 0 END"
var earthworks =  "CASE WHEN Group_Type = 'Plant and Earth Materials' THEN 1 ELSE 0 END"
var overnight =   "CASE WHEN Group_Type = 'Overnight Structures'      THEN 1 ELSE 0 END"

return Groupby(
    fs,
    ['PName'],
    [
        {name:'Total', expression: 'Group_Type', statistic: 'COUNT'},
        {name:'Fish', expression: fish, statistic: 'SUM'},
        {name:'LargeGame', expression: largegame, statistic: 'SUM'},
        {name:'SmallGame', expression: smallgame, statistic: 'SUM'},
        {name:'Bird', expression: bird, statistic: 'SUM'},
        {name:'CulturalSites', expression: culturalmat, statistic: 'SUM'},
        {name:'PlantandEarthMaterials', expression: earthworks, statistic: 'SUM'},
        {name:'OvernightStructures', expression: overnight, statistic: 'SUM'}
]
);

I probably could have thrown those expressions into the GroupBy at the bottom, but I like taller, narrow code better than really wide one-liners.

Anyway, I threw some sample data into this, and got this result:

Should be more than enough to get those popups.

[KristineHubenig]

Thank you Josh, it populated my list perfectly! And thank you for taking the time to explain the steps and logic out for me, I hope you have a great week!