topic Re: Using data expression to return duplicate values in a dashboard list in ArcGIS Online Questions

Using data expression to return duplicate values in a dashboard list

sophered — Wed, 17 Jul 2024 13:20:32 GMT

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

Re: Using data expression to return duplicate values in a dashboard list

KenBuja — Wed, 17 Jul 2024 13:50:01 GMT

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

Re: Using data expression to return duplicate values in a dashboard list

jcarlson — Mon, 22 Jul 2024 14:14:49 GMT

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, ', ')})`

Re: Using data expression to return duplicate values in a dashboard list

sophered — Wed, 17 Jul 2024 14:32:27 GMT

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?

Re: Using data expression to return duplicate values in a dashboard list

sophered — Mon, 22 Jul 2024 14:06:33 GMT

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.

Re: Using data expression to return duplicate values in a dashboard list

jcarlson — Mon, 22 Jul 2024 14:13:55 GMT

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.

Re: Using data expression to return duplicate values in a dashboard list

sophered — Tue, 23 Jul 2024 12:45:01 GMT

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?

Re: Using data expression to return duplicate values in a dashboard list

jcarlson — Tue, 23 Jul 2024 12:50:46 GMT

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.

Re: Using data expression to return duplicate values in a dashboard list

sophered — Tue, 23 Jul 2024 15:18:49 GMT

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!)

Re: Using data expression to return duplicate values in a dashboard list

jcarlson — Tue, 23 Jul 2024 15:28:30 GMT

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?

Re: Using data expression to return duplicate values in a dashboard list

sophered — Tue, 23 Jul 2024 16:02:16 GMT

Unfortunately nothing shows up in the Results when I try to return sql

Re: Using data expression to return duplicate values in a dashboard list

jcarlson — Tue, 23 Jul 2024 16:05:51 GMT

Interesting… At this point, we just need some Console statements. Run this, what does the "Messages" tab say?

var portal = Portal('portal') var fs = FeatureSetByPortalItem(portal, "itemid", 0, ['*'],true) var uniquevalues = Distinct(fs, "fieldname") Console('Unique Values', uniquevalues) var val_arr = [] for(var v in uniquevalues) { Console(v, v['fieldname']) Push(val_arr, `'${v['fieldname']}'`) Console(val_arr) } var sql = `fieldname NOT IN (${Concatenate([val_arr, ', ')})` return Filter(fs, sql)

Re: Using data expression to return duplicate values in a dashboard list

sophered — Tue, 23 Jul 2024 16:29:53 GMT

A few minutes after pressing test, I am met with this lovely screen : (

Re: Using data expression to return duplicate values in a dashboard list

jcarlson — Tue, 23 Jul 2024 17:03:10 GMT

Yikes! How many distinct values are there, anyway?

Re: Using data expression to return duplicate values in a dashboard list

sophered — Tue, 23 Jul 2024 17:13:49 GMT

Great question! Here's another yikes! When I try to do a count on the unique values, nothing shows up in the results. I can do a count on the fs (62000 records), but not unique values. I actually did open my layer up in pro and there is around 200 duplicate records when considering nulls as duplicates. If not considering nulls, there are about 3 duplicates. Unfortunately I have to display it in a list on dashboard, because the end user will not be using pro.

Re: Using data expression to return duplicate values in a dashboard list

KenBuja — Tue, 23 Jul 2024 17:31:29 GMT

The code I posted initially used a layer with 68K records, so that size of data shouldn't be a problem. Another way to cut down on the request is not to return the geometry of the features.

I'm curious on what result you got when doing a test run vs. getting the error in the Dashboard

Re: Using data expression to return duplicate values in a dashboard list

jcarlson — Tue, 23 Jul 2024 17:32:47 GMT

Oh, gosh. Well, that would explain it, we're creating a SQL string that's going to be many many thousands of characters long. It's probably the wrong approach to look for the "good" values and build a filter based on the "bad" ones.

Let's take a totally different approach. I like @KenBuja 's use of GroupBy, but the output of that won't include any of the feature attributes that we need. But it could give us one of the objectIDs with some tweaking, which we could pass into a Filter on the original featureset.

Do you know if there are any cases of more than two duplicates? This expression could probably get rid of duplicates when there's 1 extra copy.

var fs = FeatureSetByPortalItem(…) var dups = GroupBy( fs, 'some_field', [ {name: 'dup_id', expression: 'objectid', statistic: 'MAX'}, {name; 'feat_count', expression: '1', statistic: 'SUM'} ] ) var dups = Filter(dups, 'feat_count > 1') var dup_ids = [] for (var d in dups) { Push(dup_ids, d['dup_id']) } return Filter( fs, `objectid not in (${Concatenate(dup_ids, ',')})` )

Re: Using data expression to return duplicate values in a dashboard list

sophered — Tue, 23 Jul 2024 18:23:16 GMT

so i tried that expression and i once again encountered the issue where it does not give any results and does not let me press "done" to exit the expression. if including the nulls, then there are plenty of cases with more than 1 duplicate (although, id rather not include the nulls). there probably will be cases in the future where there is more than 1 duplicate and id like for it to display those as well.

Re: Using data expression to return duplicate values in a dashboard list

sophered — Tue, 23 Jul 2024 18:56:36 GMT

so this is what i tried

var portal = Portal('portal') var fs = FeatureSetByPortalItem(portal, "itemid", 0, ['*'],false) var gb = Groupby(fs, 'EXTERNALSTREETKEY', {name: 'Count', expression: '1', statistic: 'COUNT'}); var filterfs = Filter(gb, "Count > 1") return filterfs

and this is what i got! more than nothing in the results, although, my pro tells me otherwise that there should be duplicates. and i am unable to press done in the bottom left corner. i think thats the "unable to execute arcade script" kind of error thing. i did make some test data and it works perfectly (although it is a MUCH smaller dataset) but when i change the itemid to the actual dataset, it does not work

Re: Using data expression to return duplicate values in a dashboard list

sophered — Thu, 25 Jul 2024 19:25:16 GMT

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?