Arcade Data Expression - Dictionary from multiple tables to feed a chart

[GISUser74305830]

Objective: use a Data Expression to temporarily combine 2 tables into a single table to feed a chart.

Source Data:

• FMT - Available funding by FY. This has records for each FY.
• MPF - Project funding by FY. This has records for some FYs, only.

Issue: The code only returns records where the FY exists in both FMT and MPF. I tried a modified version of the code where I had it run through and calc MPF budget = 0 for each FY, then loop through each FY and Filter, but this simply added duplicate records where the FY matched for FMT and MPF. Please see attached docs for code and desired vs actual output.

[jcarlson · Accepted solution]

I was able to follow your expression well enough until Step 6, which is where I believe it's failing.  In Step 6, it looks like you've got misplaced brackets / parentheses, at least in the text file you attached. Also, you reference fields in some of the later steps that aren't part of the GroupBy functions, so I don't know where those are coming from.

Anyway, what you're looking to do is populate the output featureset with a row for every FY, regardless of whether any MPF value exists for that year. I can see you're creating a var MPF_FY, but you're then trying to use that value to filter the MPF_Grouped FeatureSet. Even though you've calculated that variable, it's not an attribute in your FeatureSet that you can use in a filter.

Also, the proper ways to reference another variable in a filter expression are to use "@varname", or to use a backtick string. Not that you can't build a filter expression string the "+" way, but your expression can be easier to read if you use either of these other methods.

"MPF_FY = '@FMT_FY'"
or
`MPF_FY = '${FMT_FY}'`

The benefit of a backtick string is that what goes between the brackets does not need to be an established variable.

It's also worth noting that you can use certain string functions in your GroupBy expressions. You can check out the list of Standardized SQL Expressions for reference. It's a shame RIGHT isn't available, but we can still make it work with SUBSTRING. For instance, you could try this for Step 4:

var MPF_Grouped = GroupBy(
    MPF,
    {
        name: 'MPF_FY',
        expression: 'SUBSTRING(mpf_fund_id, CHAR_LENGTH(mpf_fund_id)-2, 2)'
    },
    {
        name: 'MPF_Totals',
        expression: 'mpf_fund_amount',
        statistic: 'SUM'
    }
)

Since the FY in your MPF table appears to be the last two characters of the fund ID, you can simply use those last two characters as the grouping field.

But back to Step 6. What you can do, rather than have two nested for loops, is simply check if any matching FY values exist in the MPF_Grouped FeatureSet. Since we've already grouped our MPF totals by that FY value, we know that there will only be one row, if any, which matches, so we can grab that feature using First, and assign that feature's value to a variable. And by setting that variable to a 0 beforehand, we ensure that even if no MPF entries exist for the given FY, we get a value in our output.

for (var f in FMT_Grouped){
    var MPF_Budget = 0; // default value if no MPF found
    var matching_MPF = Filter(MPF_Grouped, `MPF_FY = ${f['fmt_FY']}`)

    // if any MPF exists for same FY, replace MPF_FY value
    if (Count(matching_MPF) > 0){
        MPF_Budget = First(matching_MPF)['MPF_Totals']
    }

    // Push to feature array
    Push(
        combinedDict['features'],
        {
            attributes: {
                FY: f['fmt_FY'],
                FMT_Budget: f['FMT_Totals'],
                MPF_Budget: MPF_Budget
            }
        }
    )
}

[GISUser74305830]

Thank you Josh! That worked perfectly! The below was the key missing feature that Josh provided.

var matching_MPF = Filter(MPF_Grouped, "MPF_FY = '" + FMT_FY_stripped + "'")

// if any MPF exists for same FY, replace MPF_FY value
if (Count(matching_MPF) > 0){
MPF_Budget = First(matching_MPF)["MPF_Totals"]
}