nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Použití datového výrazu k zobrazení duplicitních hodnot v seznamu na dashboardu

sophered

Dobrý den! Snažím se vyfiltrovat a zobrazit duplicitní hodnoty v poli v prvku seznamu na dashboardu. Napadlo mě, že bych k tomu mohl použít funkci distinct, ale zatím se mi to nepodařilo. Zatím mám toto!

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

Najděte více příspěvků označených jako

Arcade

Accepted answers

All comments

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");

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?

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

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?

sophered

after some tinkering, I found out that other fields on the same layer work perfectly fine with using the Groupby Method. The particular field that i am trying to use for this was a mixed case kinda field with special characters (@, .) I am unsure if this has a factor in it or not but it is interesting that it works perfectly for other fields as well, but not this one! I was wondering if i could try to use a Left function to pull only the first 10 characters, those are the only ones that need to be checked for unique-ness. and groupby those?

jcarlson

Oh, interesting. Yeah, you could try that.

distinct(
  fs,
  {name: 'some-field', expression: 'LEFT(some-field, 10)'}
)

Whether or not "LEFT" works depends on the underlying database. You can try SUBSTRING, too.

https://doc.arcgis.com/en/arcgis-online/reference/sql-agol.htm

LEFT isn't in the list, but that list isn't comprehensive. If the function is available in your RDBMS, you should be able to use it in Arcade.

Připojte se ke konverzaci

Přihlášení členové mohou přispívat, sledovat aktualizace a další. Jste tu noví? Zaregistrujte si bezplatný účet.

Rychlé odkazy

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.