topic Re: Using SQL in an Arcade GroupBy expression in ArcGIS Online Questions

Using SQL in an Arcade GroupBy expression

JasonBatory — Thu, 19 Sep 2024 08:25:59 GMT

Can anyone confirm whether expressions like this should work, or if my syntax is just wrong?

In my Arcade groupBy function I want to use SQL in the expression, like this:

var fs = FeatureSetByPortalItem( Portal("https://www.arcgis.com"), "6200db0b80de4341ae8ee2b62d606e67", 0, ["*"], false ); //For each building FeatureCode //Count how many buildings with each FloorCount return GroupBy(fs, 'FEATURECODE', [{name: 'Floors_0', expression: 'FLOORCOUNT = 0', statistic: 'Count'}, {name: 'Floors_1', expression: 'FLOORCOUNT = 1', statistic: 'Count'}, {name: 'Floors_2', expression: 'FLOORCOUNT = 2', statistic: 'Count'},])

This returns an error:

Test execution error: Unknown Error. Verify test data.

I've tried various different syntax for the 'FLOORCOUNT = 0' statement with the same results.

Re: Using SQL in an Arcade GroupBy expression

CodyPatterson — Thu, 19 Sep 2024 11:11:29 GMT

Hey @JasonBatory

Could you give this here a shot? You may want to use Iif in terms of the expression:

var fs = FeatureSetByPortalItem( Portal("https://www.arcgis.com"), "6200db0b80de4341ae8ee2b62d606e67", 0, ["*"], false ); return GroupBy(fs, 'FEATURECODE', [{name: 'Floors_0', expression: IIf($feature.FLOORCOUNT == 0, 1, 0), statistic: 'Sum'}, {name: 'Floors_1', expression: IIf($feature.FLOORCOUNT == 1, 1, 0), statistic: 'Sum'}, {name: 'Floors_2', expression: IIf($feature.FLOORCOUNT == 2, 1, 0), statistic: 'Sum'}])

The 0, 1, 0 in the first one, is the values of the condition, true value, and false value, so if it is 0 it will return true, otherwise false.

Let me know if this helps out!

Cody

Re: Using SQL in an Arcade GroupBy expression

jcarlson — Thu, 19 Sep 2024 13:07:15 GMT

In GroupBy, the "expression" key is specifically looking for valid SQL.

@JasonBatoryin your series of expressions, you're treating the "expression" like it's a filter. In GroupBy, it's going to attempt to return the statistic from the expression, and you can't really count whether or not the floorcount is equal to a value.

To use @CodyPatterson 's idea, but in SQL, you can use CASE:

"CASE WHEN FLOORCOUNT = 0 THEN 1 ELSE 0 END"

Use that with the statistic "SUM", and you'll get the number of records with that particular floor count value.

Re: Using SQL in an Arcade GroupBy expression

JasonBatory — Thu, 19 Sep 2024 14:22:16 GMT

Ok, this makes sense now. I also tried "WHERE FLOORCOUNT = 0" with count statistic, but I see this should be treated more like a decode situation using the sum statistic.

Thanks for your help Josh, and Cody.

Re: Using SQL in an Arcade GroupBy expression

JasonBatory — Thu, 19 Sep 2024 14:33:34 GMT

Follow up question, is there any way to add a "total" row at the bottom displaying the total of each of the columns "Floors_0", "Floors_1", etc?

Re: Using SQL in an Arcade GroupBy expression

jcarlson — Thu, 19 Sep 2024 14:45:24 GMT

Not really. The number of rows in the output are based on the unique values in your second parameter, 'FLOORCOUNT'. There's probably some way to do a second GroupBy and merge it with the first, but it's probably not worth the trouble.

If you're bringing this into a Dashboard, there are lots of widgets you could use to display the totals of each of those columns. Is there a reason you want the total as a separate row in the table itself?

Re: Using SQL in an Arcade GroupBy expression

JasonBatory — Thu, 19 Sep 2024 14:55:23 GMT

Yeah this is in a Dashboard, I was basically hoping to just emulate a pivot table, not a big deal.

I guess I can just rethink the design and present those totals in another way.

Thanks again.