Problem with defining variables with multiple functions from one featureset as a data expression.
748
3
09-20-2022 10:19 PM
by
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
- this will lead to problems if your date is a text value, because text values are sorted differently
- "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], "\t")
}}
Push(outputDict.features, newFeature)
prev = chemical
}
}
// convert the dictionary to a featureset and return
return Featureset(Text(outputDict))
by
