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; } } }

ダッシュボードのリストで重複する値を返すデータ式の使用

sophered

こんにちは！ダッシュボードのリスト要素で、フィールド内の重複する値を抽出して表示しようとしています。これを行うにはDistinct関数を使えばよいと思ったのですが、うまくいきませんでした。現時点での式はこちらです！

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

タグ付きの投稿をもっと見る

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.

会話に参加する

サインインしたメンバーは投稿、更新のフォローなどができます。初めてですか？無料アカウントを登録してください。

クイックリンク

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