Problem with defining variables with multiple functions from one featureset as a data expression.

Question

Hi everyone,

I am a bit new to using Arcade and I was wondering if I could get some assistance. I have a singular feature set which contains a date field (as a date type), and a string field for a device name (of which there are multiple) and a double field which contains values of percentage of a chemical that we are measuring. Basically we have devices A, B and C out in a field measuring for a chemical and the table gets updated every month with the date data is manually captured and the chemical data percent value added next to each new line for each device. Due to the software that our field team uses, the date information is captured with a time stamp.

The data would look something like this:

DeviceDateChemical %A101/04/202250A201/04/202249.8A301/04/202251.2A101/05/202249.2A201/05/202251.2A301/05/202248.2

As you can see, the table keeps expanding with new data being added to it based on that month's readings.

With the guidance of the following support article I am trying to organise my data to show a list of the devices and their monthly change:

https://www.esri.com/arcgis-blog/products/ops-dashboard/analytics/enhancing-dashboard-elements-using-data-expressions-part-2/

However I am finding that I am having syntax issues when trying to filter the FeatureSet by date when defining a variable. At first I thought it was an issue with the date field, so i created a new field called DateText and converted the feature to a normal string field. It does not seem to error with defining the below currentDate and monthAgo variables however when trying to define the today and lastMonth variable it is throwing syntax errors:

var MethaneReport = FeatureSetByPortalItem(Portal('https://ORGANISATION.maps.arcgis.com'),"FEATURE-SET-UUID", 0,["*"],false);

var combinedDict = {
fields: [
{name: "Device", type: "esriFieldTypeString"},
{name: "Date", type: "esriFieldTypeString"},
{name: "Chemical", type: "esriFieldTypeString"},
{name: "current", type: "esriFieldTypeString"},
{name: "month_ago", type: "esriFieldTypeString"},
],
geometryType: "",
features: [],
};

// Iterate through features to find most current date
var currentDate = Text(Date(Max(combinedDict, 'DateText')), "DD-MM-YYYY");
var monthAgo = Text(DateAdd(currentDate, -31, "days"), "DD-MM-YYYY");

// Use most current date as a filter
var today = OrderBy(Filter(combinedDict, "(DateText= @currentDate) AND (Device_ID NOT IN ('A4','A5','A6'))"), 'Device_ID');
// Filter and retrieve rows that correspond to a month ago
var lastMonth = OrderBy(Filter(combinedDict, "(DateText = @monthAgo) AND (Device_ID NOT IN ('A4','A5','A6'))"), 'Device_ID');

The fields in the tables are defined as Device_ID and DateText.

It is all looking fine up until I start to include the today and lastMonth variables into the code and it errors as below:

Execution Error:Filter cannot accept this parameter type.

I am sure that the syntax/logic I am using is wrong but I cannot quite spot where. I was wondering if anyone could please lend some advice?

JohannesLindner · Answer

Oh, I should have clarified that: I'm just defining some example data to work with there. The "real" script starts at line 21, but instead of converting the input data to a Featureset, you would load your data from the Portal or ArcGIS Online:

var methaneReport = FeaturesetByPortalItem(Portal("https://portal.url"), "service-guid", "layer-id")

TomGrantham · Answer

Hi Johannes,

Sorry for the late reply, it's been a busy one on my end!

Just looking through your code you seem to be identifying the actual chemical values as a feature in your input variable:

var input = {
    geometryType: "",
    fields: [
        {name: "Device_ID", type: "esriFieldTypeString"},
        {name: "DateText", type: "esriFieldTypeString"},
        {name: "Chemical", type: "esriFieldTypeDouble"},
        ],
    features:[
        {attributes: {Device_ID: "A1", DateText: Text(DateAdd(Now(), -1, "months"), "DD/MM/YYYY hh:mm:ss"), Chemical: 50}},
        {attributes: {Device_ID: "A2", DateText: Text(DateAdd(Now(), -1, "months"), "DD/MM/YYYY hh:mm:ss"), Chemical: 49.8}},
        {attributes: {Device_ID: "A3", DateText: Text(DateAdd(Now(), -1, "months"), "DD/MM/YYYY hh:mm:ss"), Chemical: 51.2}},
        {attributes: {Device_ID: "A1", DateText: Text(Now(), "DD/MM/YYYY hh:mm:ss"), Chemical: 49.2}},
        {attributes: {Device_ID: "A2", DateText: Text(Now(), "DD/MM/YYYY hh:mm:ss"), Chemical: 51.2}},
        {attributes: {Device_ID: "A3", DateText: Text(Now(), "DD/MM/YYYY hh:mm:ss"), Chemical: 48.2}},
        ]
}

This probably wont work in this use case as the sample table is continually updated each month with new rows of data for each sample point (device). So A1, A2, A3, A4, A5 and A6 will be added again as new rows with a different date with time stamp in the same date field. So I am looking to make a script that will adapt to this organic growth. Would you have any suggestions?

Regards,

Tom

JohannesLindner · Answer

To post code:

* You're using combinedDict as input for Filter() and Max(). This is wrong, because* these functions work on FeatureSets, combinedDict is a Dictionary
* combinedDict is your output, you need to use your input (MethaneReport).

* To get the date 1 month ago, use DateAdd(baseDate, -1, "months")
* Max() only works for numbers. If your date column is Text (as the name DateText implies), it will return NaN.
* To get the most current date, you could use First(OrderBy(methaneReport, "DateText DESC")), but* this will lead to problems if your date is a text value, because text values are sorted differently* "02/04/2022" > "01/05/2022"

* this will lead to problems if the values of the devices are collected on different days

* "Due to the software that our field team uses, the date information is captured with a time stamp." Does this mean that the date field doesn't look like in your table but rather like this: "01/04/2022 10:25:59"? Then your Filter() statement won't work, because "01/04/2022" != "01/04/2022 10:25:59"

Assuming your input layer looks like this:

// https://community.esri.com/t5/arcgis-online-questions/problem-with-defining-variables-with-multiple/m-p/1214482#M47979

var input = {
    geometryType: "",
    fields: [
        {name: "Device_ID", type: "esriFieldTypeString"},
        {name: "DateText", type: "esriFieldTypeString"},
        {name: "Chemical", type: "esriFieldTypeDouble"},
        ],
    features:[
        {attributes: {Device_ID: "A1", DateText: Text(DateAdd(Now(), -1, "months"), "DD/MM/YYYY hh:mm:ss"), Chemical: 50}},
        {attributes: {Device_ID: "A2", DateText: Text(DateAdd(Now(), -1, "months"), "DD/MM/YYYY hh:mm:ss"), Chemical: 49.8}},
        {attributes: {Device_ID: "A3", DateText: Text(DateAdd(Now(), -1, "months"), "DD/MM/YYYY hh:mm:ss"), Chemical: 51.2}},
        {attributes: {Device_ID: "A1", DateText: Text(Now(), "DD/MM/YYYY hh:mm:ss"), Chemical: 49.2}},
        {attributes: {Device_ID: "A2", DateText: Text(Now(), "DD/MM/YYYY hh:mm:ss"), Chemical: 51.2}},
        {attributes: {Device_ID: "A3", DateText: Text(Now(), "DD/MM/YYYY hh:mm:ss"), Chemical: 48.2}},
        ]
}

// load your input data
var methaneReport = Featureset(Text(input))

// filter out the unwanted devices
methaneReport = Filter(methaneReport, "Device_ID NOT IN ('A4', 'A5', 'A6')")

// get all Device_IDs
var devices = OrderBy(Distinct(methaneReport, "Device_ID"), "Device_ID")

// define the output
var outputDict = {
    geometryType: "",
    fields: [
        {name: "Device_ID", type: "esriFieldTypeString"},
        {name: "DateText", type: "esriFieldTypeString"},
        {name: "Chemical", type: "esriFieldTypeDouble"},
        {name: "Change", type: "esriFieldTypeDouble"},
        {name: "ListLabel", type: "esriFieldTypeString"},
        ],
    features:[]
}

// define a function to convert your date format to an actual date
function textToDate(textVal) {
    // assuming your format is "DD/MM/YYYY hh:mm:ss"
    var dateVal = Split(textVal, " ")[0]
    var dateSplit = Split(dateVal, "/")
    var dd = Number(dateSplit[0])
    var mm = Number(dateSplit[1]) - 1  // month starts at 0
    var yy = Number(dateSplit[2])
    return Date(yy, mm, dd)
}

// define a function that sorts an array of [ [Date, Chemical] ]
// see https://developers.arcgis.com/arcade/function-reference/data_functions/#sort
function sortByDate(a, b) {
    return a[0] > b[0]
}

// loop through the Devices
for(var device in devices) {
    var deviceID = device.Device_ID
    // get all measurements for this device
    var deviceMeasurements = Filter(methaneReport, "Device_ID = @deviceID")
    // sort the measurements by date
    // because DateText is a text field, we can't simply oder by DateText
    // instead we have to extract DateText (converted to date) and Chemical and sort that array
    var values = []
    for(var m in deviceMeasurements) {
        Push(values, [textToDate(m.DateText), m.Chemical])
    }
    var sortedValues = Sort(values, sortByDate)
    // fill in the output dict
    var prev = sortedValues[0][1]
    for(var i in sortedValues) {
        var dateText = Text(sortedValues[i][0], "YYYY-MM-DD")
        var chemical = sortedValues[i][1]
        var change = Round(chemical - prev, 2)
        var changeText = "(" + When(change == 0, "-", change > 0, "+" + change, change) + ")"
        var newFeature = {attributes: {
            Device_ID: deviceID,
            DateText: dateText,
            Chemical: chemical,
            Change: change,
            ListLabel: Concatenate([deviceID, dateText, chemical, changeText], "	")
        }}
        Push(outputDict.features, newFeature)
        prev = chemical
    }
}

// convert the dictionary to a featureset and return
return Featureset(Text(outputDict))