Using data expression to return duplicate values in a dashboard list

[sophered]

Hello! I am trying to isolate and display duplicate values in a field in a dashboard list element. I was thinking of using the distinct function to do this but have not had any luck. Here is what I have so far! 

var portal = Portal('portal')
var fs = FeatureSetByPortalItem(portal, "itemid", 0, ['*'],true)
var uniquevalues = Distinct(fs, "fieldname")
return Filter(fs, "fieldname NOT IN @uniquevalues")

[KenBuja]

You can use GroupBy, getting the count of each attribute in your field, then returning the counts above 1.

var fs = FeatureSetByPortalItem(
  Portal("https://www.arcgis.com"),
  "6200db0b80de4341ae8ee2b62d606e67",
  0,
  ["*"],
  true
);
var gb = GroupBy(
  fs,
  ["FLOORCOUNT"],
  { name: "Count", expression: "1", statistic: "COUNT" }
);
Filter(gb, "Count > 1");

[sophered]

I tried this approach and unfortunately it gives me an "unable to execute arcade script error." it seems to work when i test run it.

[jcarlson]

You're close, but your filter statement isn't going to work. Using Distinct against a FeatureSet returns another FeatureSet. Even if it returned an array, your filter expression needs to be SQL, where IN is formatted:

IN ('value 1', 'value 2', 'etc')

We can get there in just a few more lines. Put this between the third and fourth lines and see what happens.

// loop through distinct values and pouplate SQL string
var val_arr = []

for (var v in uniquevalues) {
  Push(val_arr, `'${v['fieldname']}'`
}

var sql = `fieldname NOT IN (${Concatenate(val_arr, ', ')})`

[sophered]

I'm getting a parse error on the sql variable line 

var portal = Portal('portal')
var fs = FeatureSetByPortalItem(portal, "itemid", 0, ['*'],true)
var uniquevalues = Distinct(fs, "fieldname")
var val_arr = []
for(var v in uniquevalues) {
    Push(val_arr, `'${v['fieldname']}'`)
}

var sql = `fieldname NOT IN (${Concatenate([val_arr, ', ')})`
return Filter(fs, sql)

is there something that I'm doing wrong here?  

[jcarlson]

That was my typo. There should be no square bracket in the Concatenate function.

var sql = `fieldname NOT IN (${Concatenate(val_arr, ', ')})`

 I'll edit the original post to reflect the correction.

[sophered]

unfortunately when i test the expression, it gives me nothing in the results. my dataset is quite large (~60,000 records), could this be an issue? 

[jcarlson]

Distinct uses SQL; on the back-end, it's a really simple expression that the server can evaluate quickly. Looking for distinct values in a single field with that many records should not take long at all.

Another suggestion: try limiting the fields and geometry coming in through the FeatureSet function. That will cut back on the potential data being transferred by the server, and usually speeds things up.

Can you try just returning the distinct values and seeing what it looks like?

var portal = Portal('portal')
var fs = FeatureSetByPortalItem(portal, "itemid", 0, ['fieldname'], false)
return Distinct(fs, "fieldname")

 Also, any chance this layer is public? Testing the expression against the real data would be helpful. I can write a similar expression against my own data which works just fine.

[sophered]

Thank you for the valuable information about the distinct function! By just returning distinct values, it is able to pull up records from the specified field that are unique. Unfortunately the layer is not public. It should be noted, however that I am working off of version 10.9.1, so 1.12 for arcade (meant to put this post under the enterprise portal questions but accidentally put it under arcgis online, sorry!)

[jcarlson]

No worries! At 1.12, you should have access to all the functions you need for this.

Back on the original expression I suggested, what do you see if you try return sql before the last line?