Arcade: Decode within GroupBy exression

[AlexandHall95]

Hopefully someone has some advice, I have a a bit of arcade that I can't seem to get to work. The arcade script runs eternally without returning a result:

return GroupBy(

    filteredFs,

    ['lead_contractor_name'],

    [

        {name: 'failed_count', expression: 'Decode(state, "FAILED", 1, 0)', statistic: 'SUM'},

        {name: 'successful_count', expression: 'Decode(state, "SUCCESSFUL", 1, 0)', statistic: 'SUM'}

    ]

);

I have done some debugging and the filteredFs returns as expected, however when attempting to return the GroupBy output, nothing is returned and no errors are flagged.

[jcarlson · Accepted solution]

GroupBy is one of those functions that uses SQL. The value of expression has to be a valid SQL expression, not Arcade. Thankfully, Decode is something we can easily replicate in SQL using a CASE statement.

return GroupBy(
    filteredFs,
    ['lead_contractor_name'],
    [
        {name: 'failed_count', expression: "CASE WHEN state = 'FAILED' THEN 1 ELSE 0 END", statistic: 'SUM'},
        {name: 'successful_count', expression: "CASE WHEN state = 'SUCCESSFUL' THEN 1 ELSE 0 END", statistic: 'SUM'}
    ]
);

[AlexandHall95]

Brilliant, thank you!