Return non-grouped values in a GroupBy expression

[GregKeith]

Hello,

I have a list with a data expression that correctly returns the count of various sample_types. I'd like to also return two non grouped fields, site_name and sample_date. I don't need statistics on them. They wouldn't be used for display, but only to allow other elements to filter by either of them.

I'm not sure this can be done; it can't in SQL, afaik, due to the underlying set theory. If I add them to the GroupBy clause, I get every unique combination of all three fields, which I don't want. Is there a way to accomplish this somehow? If not, that would be good to know as well. Thanks.

[JohannesLindner]

AFAIK, each field in a GroupBy (be it in Arcade or SQL) is either part of the group or part of the statistics.

If I add them to the GroupBy clause, I get every unique combination of all three fields, which I don't want

Well, that seems logical to me. Say we have the sample types ["S1", "S2", "S3"] and the sites ["A", "B", "C", "D"] (we'll skip the dates)

You can get statistics on the sample types ("There were 123 samples of type S1, 2 samples of type S2, 34 samples of type S3").

You can get statistics on the type and site: "There were 25 S1 samples at A, 50 at B, 25 at C, 23 at D. etc."

If you don't put the sites into the group too, the sample type loses its relationship to the site. So how do you want to relate them? Or do you just want to return distinct values for sites and dates?

[GregKeith]

Thanks @JohannesLindner . Ideally I want the list to show counts for all sample types for all records (which it does now) but also have the ability to be filtered to just a specific site via the Action section of another list of sites. This requires a site field in the sample count list, but I don't want to group by that.

Current

Sample Type	Sample Count
Dup	122
Field	79

Filtered for a site

Sample Type	Sample Count	Site (showing site is optional)
Dup	14	Chicago
Field	8	Chicago

Hope that makes sense

[JohannesLindner · Accepted solution]

Ah. For that, you need two widgets:

The Table Widget:

• Use your raw layer as input
• Set table type to "Grouped Values", group by sample type, define a count field

The Category Selector Widget:

• can only be added to a Top/Sidebar Widget, so add that first
• set to "Grouped Values", use the same layer you used for the Table widget
• group by site
• add an action to filter the Table widget