Use Distinct in Arcade Expression to return unique rows from table

[DonSjoboen]

Hello,

I'm trying to use DISTINCT in my Arcade expression (see below) in my web map popup, but am not getting the expected results.

I'm trying to return the unique route for each customer.  I'm relating this table to a point feature class.  I published my map service (to our ArcGIS Servers) with the point feature class and the two view tables, and using the FeatureSetByName $datastore for the var tbl.

When I Console(customers) to view the results, I get 'Object, FeatureSet'?  Does Distinct work on feature classes only or does it work with tables?

Here's my Arcade expression...

var tbl = FeatureSetByName($datastore,"SWM Routes Table");
Console(Count(tbl));
var id = $feature["CO_ID"];
Console(id);
var sql = "CONNECTION_OBJECT = '" + id + "'";
Console(sql);
var customers = Distinct(Filter(tbl, sql), "ROUTE");
Console(customers);
var cnt = Count(customers);
Console(cnt);
var result = "";
if (cnt > 0) {
for (var customer in customers) {
result += TextFormatting.NewLine + "Route: " + customer.ROUTE +
" / " + customer.Supervisor + " / " + customer.COLLECTION_DAY +
/* TextFormatting.NewLine + "Customer: " + customer.BP_Name +
TextFormatting.NewLine + "Business Partner: " + customer.BP_Num +
TextFormatting.NewLine + "Contract Account: " + customer.ContractAcct +*/
TextFormatting.NewLine;
}

} else {
result = "No customer data"
}

Console(result);
return result;

Anyone have experience with using Distinct in a related (1:M) table or have suggestions?

Thanks,

Don

[DavidColey]

Hi @DonSjoboen and @XanderBakker  - I know you guys were working on something a little different with this a while back but I wanted to share something similar since this post helped me out:

var intParcels = Intersects($feature, FeatureSetByName($map, 'Community Flood Zone'));
var fpTypes = Distinct(intParcels, "zonetype");
var fpCnt = Count(fpTypes);
var fplist = '';

if (fpCnt > 0){
        for (var k in fpTypes)
        {
            fplist += ' Zone ' + k.zonetype;
        }
    
    } else {
        fplist = 'No Flood Zone intersects this parcel'
    }

return fplist;

In my case, click on a parcel, return flood info from the Intersects.  Add a TextFormatting.NewLine at where needed.

Thanks-

David

[XanderBakker]

Hi @DavidColey ,

You can do something like this (see below). Remember that the Distinct function returns and array and not a featureset:

var intParcels = Intersects($feature, FeatureSetByName($map, 'Community Flood Zone'));

// this returns an array, not a featureset (and I sorted it)
var fpTypes = Sort(Distinct(intParcels, "zonetype")); 
var fpCnt = Count(fpTypes);
var fplist = '';

if (fpCnt > 0) {
    // create empty array
    var lst = [];
    // add the required text to the zones
    for (var i in fpTypes) {
        Push(lst, '- Zone: ' + fpTypes[i]);
    }
    // concatenate with new line
    fplist = Concatenate(lst, TextFormatting.NewLine);    
} else {
    fplist = 'No Flood Zone intersects this parcel'
}

return fplist;

[DavidColey]

fyi @XanderBakker , this returns an Illegal Argument error in the Map Viewer Arcade editor.  But that's ok because I'm not treating it as an array.  However, if you have an idea on how to get DomanName descriptions to return from the Distinct that would help. 

Because FeatureSetByName here is using the $map profile, I can't seem to get the Distinct to return coded value descriptions, as if I were using the $feature profile....

[XanderBakker]

Hi @DavidColey 

One way to go is to get the code a translate it to the description making use of the domain function. You can use something like the example below to get a dictionary of the domain and use that dictionary to translate the description based on the code.

function GetDomainDct(lay, fldname) {
    var dom = Domain(lay, fldname);
    var cvs = dom["codedValues"];
    var dct = {};
    for (var i in cvs) {
        var cv = cvs[i];
        dct[cv["code"]] = cv["name"];
    }
    return dct;    
}

[DavidColey]

Ah yes thanks @XanderBakker  - I was looking at DomainName, but realized I had no method to get a return of descriptions.  I'll work on this between dev conference sessions this week and post back what I can put together.

[DavidColey]

Hi @XanderBakker  - here's some of what I came up with: 

var intParcel = Intersects(Centroid($feature), FeatureSetByName($map,"County Zoning", ['zoningdesignation'], true));
var fldname = 'zoningdesignation';

var znType = Distinct(intParcel, fldName);
var znCnt = Count(znType);
var znlist = '';

function GetDomainDct() {
    var dom = Domain(intParcel, fldname);
    var cvs = dom["codedValues"];
    var dct = {};
    for (var i in cvs) {
        var cv = cvs[i];
        dct[cv["code"]] = cv["name"];
    }
    return dct;    
}
var rt = GetDomainDct();

if (znCnt > 0){
  for (var k in znType) {
        znlist = rt[k.zoningdesignation]; //needs to be an array
            
    }
} else {
      znlist = 'Zoning Information Missing'
    }
return znlist;

I was struggling with how to return the function until I realized I needed to set it to a var (via the Arcade Help) and then from that point it was simply a matter of making sure list was set to return an array as well.  I have another piece that does not use the centroid, but then removes the self-intersecting zoning type, and returns the types that are adjacent:

var lstZoning = FeatureSetByName($map, 'County Zoning', ['zoningdesignation'], true);
var intParcel = $feature;
var numZones = Count(Intersects(lstZoning, intParcel));
var intZoning = Intersects(Centroid(intParcel),lstZoning);
var allZoning = Intersects(intParcel,lstZoning);

var znTypes = Distinct(intZoning, "zoningdesignation");
var allTypes = Distinct(allZoning, "zoningdesignation");
var initialZT = "";
var znlist = '';

function GetInitDomainDct() {
    var dom = Domain(intZoning, "zoningdesignation");
    var cvs = dom["codedValues"];
    var dct = {};
    for (var i in cvs) {
        var cv = cvs[i];
        dct[cv["code"]] = cv["name"];
    }
    return dct;    
}
var rt = GetInitDomainDct();

function GetDomainDct() {
    var domAll = Domain(allZoning, "zoningdesignation");
    var cvsAll = domAll["codedValues"];
    var dctAll = {};
    for (var j in cvsAll) {
        var cvAll = cvsAll[j];
        dctAll[cvAll["code"]] = cvAll["name"];
    }
    return dctAll;    
}
var rtAll = GetDomainDct();

for (var k in znTypes)
    {
        initialZT = rt[k.zoningdesignation];
    }
    
if (numZones > 0){
    for (var m in allTypes){
        znlist += TextFormatting.NewLine + rtAll[m.zoningdesignation];
    }
} else {
    znlist = 'No zoning intersects this parcel'
}

var noZone = 'No zoning types border this parcel'
var newList = TextFormatting.NewLine + Trim(Replace(znlist,initialZT,"")); //Trim removes blank space left from replace

if (IsEmpty(Trim(Replace(znlist,initialZT,"")))){
    return noZone;
} else {
    return newList;
}

The only problem is, is that it has to run things twice.  If you have any ideas on how to make this more concise that would be great -