Return non-grouped values in a GroupBy expression

463
3
Jump to solution
05-04-2023 12:01 PM
Labels (1)
GregKeith
Occasional Contributor III

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.

0 Kudos
1 Solution

Accepted Solutions
JohannesLindner
MVP Frequent Contributor

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

JohannesLindner_0-1683970248456.png

 

 

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

JohannesLindner_1-1683970473526.png

JohannesLindner_2-1683970515122.png

 

 

JohannesLindner_3-1683970584762.pngJohannesLindner_4-1683970597690.pngJohannesLindner_5-1683970612835.png

 


Have a great day!
Johannes

View solution in original post

3 Replies
JohannesLindner
MVP Frequent Contributor

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?


Have a great day!
Johannes
0 Kudos
GregKeith
Occasional Contributor III

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 TypeSample Count
Dup122
Field79

 

Filtered for a site

Sample TypeSample CountSite (showing site is optional)
Dup14Chicago
Field8Chicago

 

Hope that makes sense

0 Kudos
JohannesLindner
MVP Frequent Contributor

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

JohannesLindner_0-1683970248456.png

 

 

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

JohannesLindner_1-1683970473526.png

JohannesLindner_2-1683970515122.png

 

 

JohannesLindner_3-1683970584762.pngJohannesLindner_4-1683970597690.pngJohannesLindner_5-1683970612835.png

 


Have a great day!
Johannes