Add fields from another datasets Arcade

[AdrianPatrulescu]

var features = FeatureSetByPortalItem( Portal('https://www.arcgis.com'), '98cca0ba2d58470b96061faa24421e66', 0, ['ID_FCC', 'RestorationArea','RestorationType','Project','HabitatRestored','Date'], false );
var filterFeatures = Filter(features, "RestorationType <> 'Riparian'");
var distinctFeatures = Distinct(filterFeatures,['ID_FCC','RestorationArea']);
return distinctFeatures

The above feature set return only unique  combination between ID_FCC  and RestorationArea (witch is what I want) columns together with ROW_ID

But I want to return also the fields from FeatureSetByPortalItem (eg 'RestorationType', 'Project', 'HabitatRestored' and 'Date')

Apparently Distinct Function remove other columns 

Is there a way to rejoin all the fields base on ROW_ID?

Thanks

Adrian

[XanderBakker]

Hi @AdrianPatrulescu ,

The distinct function only returns the values that are used in the distinct function. You can include the other fields in the distinct function, but if you have multiple values for the other fields for each distinct 'ID_FCC' and 'RestorationArea' combination you will need to determine what you want to do with this (which value to return). 

[AdrianPatrulescu]

Hi Xander,

Thank you for your reply,

For each distinct 'ID_FCC' and 'RestorationArea' combination I want to make a SUM of  column 'RestorationArea'

But also I want to be able to filter that SUM depending on 'RestorationType', 'Project', 'HabitatRestored' or 'Date'

The distinct function is want I want in terms of unique combination between ID_FCC' and 'RestorationArea' but I want to add the above fields to the distinctFeatures dataset to dynamically filter the RestorationArea SUM depending on 'Date' AND/OR 'RestorationType' AND/OR 'Project' AND/OR 'HabitatRestored'

If I add the fields to the distinct function I will lose the unique combination between 'ID_FCC' and 'RestorationArea'

Basicaly a want to filter the SUM of RestorationArea base on other fields which are not in distinctFeatures dataset but exist in filterFeatures (like a join if it is possible)

Thanks

Adrian

[XanderBakker]

Hi @AdrianPatrulescu ,

This is possible but will require some more scripting and collecting all the data in a different data format so you can return a featureset that includes all the information and allows the filtering you need.

[AdrianPatrulescu]

Hi @XanderBakker ,

Thank you for your response

Can you help me with the next steps to obtain desired filtering?

[XanderBakker]

Hi @AdrianPatrulescu ,

I can help, but that would require access to the data. There are a couple of things I have to see in the data to verify if what you want to achieve is possible.

If the data is published in ArcGIS Online, you can create a group and share the data with the group and invite me to the group using "xbakker.spx" as user name.

[AdrianPatrulescu]

Done

[XanderBakker]

Hi @AdrianPatrulescu ,

I just had a look at the data, and below you will find a specific case so you can walk me through it:

It is a single ID_FCC with multiple RestorationArea values in which for two cases the area is repeated. You will see two additional fields SUM (sum of the RestorationArea) and Count (frequency). I also included the other fields you are interested in, but they seem to have the same values. For this specific case, what type of queries do you need to perform?

[AdrianPatrulescu]

Thank you for taking time to take a look to the data

For the above dateset i need to make a total SUM of the column RestorationArea (exclude repeating Unique Code - for example I want to add only once 183n - 7,71) and that sum can be filtered depending on the columns: 'Date' 'RestorationType'  'Project'  'HabitatRestored' (in this case are the same but for the all data they are differences) - For example want to know the total restoration area (add only once duplicates) for the project OAK or the total restoration area (add only once duplicates) between date x and date y

duplicates = areas where we had to intervene for 2 or 3 times and I want to add the area only once

Adrian

[XanderBakker]

Hi @AdrianPatrulescu ,

Let me see if I get this right. In the example of "FC 183n" you want to obtain 7 records (not include duplicates) and calculate the sum of all the RestorationArea for "FC 183n", which in this case would be "129.12" and not "152,68".

If so, for the future it would be better to store the interventions in a related table and not duplicate the areas. Maybe it would be possible to create a copy of the dataset using the Delete Identical (Data Management) in ArcGIS Pro.

It is possible to do this (manually) using Arcade, but before investing time in programming, it is best to evaluate other less complex options.