I am looking for some help regarding an Arcade script that I am developing for the ESRI Dashboard List Widget. Essentially, I have a hosted feature table that has duplicate records based on "unique_user" but must remain split due to some non-duplicate attributes. I would like to only display only one DISTINCT record, based on "unique_user." As long as the script only returns one $datapoint then that is fine - the index does not matter.
Here is what I have so far, any thoughts on how to only return one distinct value per "unique_user"?
// different categories of days anticipated
var perm_status = $datapoint["perm_status"]
var dateTechReviewComplete = Date($datapoint["dt_tech_review_complete"])
// extra variables
var dateSubmitted = Date($datapoint["dt_submitted"])
var nowDate = Date(Now())
if (perm_status == "Technical Review Complete") {
var tech = DateAdd(dateTechReviewComplete, 195, 'days')
var daysAnt = Text(tech, "MMMM D, Y")
var dateDifference = Round(DateDiff(tech, nowDate, 'days'))
// Return only one DISTINCT date based upon "unique_user"
}
else {
var daysAnt = "No Permit Status Reported"
return True
}
return {
textColor: '',
backgroundColor: '',
separatorColor:'',
selectionColor: '',
selectionTextColor: '',
attributes: {
daysAnticipated: daysAnt,
permitStatus: perm_status,
daysDifference: dateDifference
}
}
This is what the code currently returns, duplicates have a red mark next to them:
The desired result is that only one of the duplicate rows is returned.
To get distinct values, you need to either:
In the Advanced Formatting part of the widget, your Arcade expression can only evaluate per-row, and it can't "see" other features in the layer.
Thanks! Do you know how I would write a data expression to filter?
You can find a bunch of examples here:
https://github.com/Esri/arcade-expressions/tree/master/dashboard_data
Your particular case is easy enough, since the requirement is to remove duplicates. There is an Arcade function Distinct, which returns a FeatureSet.
Try this:
var fs = FeatureSetByPortalItem(
Portal('your portal url'),
'itemid of the service',
0,
['fields', 'you', 'want'],
false
)
return Distinct(
fs,
['fields', 'you', 'want']
)
Thanks for the link, really helpful!
When attempting to return Distinct values, I am getting this Console error. We only have Enterprise 10.9.1 - do you think that may be a reason the analysis isn't supported?
What you're seeing is a result of the particular Profile you're using.
https://developers.arcgis.com/arcade/profiles/
In the Advanced Formatting, you cannot use FeatureSet functions. To use a Data Expression, you've actually got to go all the way back to where you choose a data layer for the widget, then say "New Data Expression" there.