Using SQL in an Arcade GroupBy expression

[JasonBatory]

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.

[CodyPatterson]

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

[jcarlson · Accepted solution]

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.

[JasonBatory]

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.

[JasonBatory]

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?

[jcarlson]

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?

[JasonBatory]

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.