Hi !
I have a point hosted feature layer called "Fitting_Collection" which is being used in data collection using field maps. I have created a dashboard to track the progress of the field operations.
Now i am trying to create a list of the category of the fittings and the count of them using Arcade groupBy functions in the list element of dashboard (Data Expression).
Since the category column ("Fitting_Type_DMZ") is subtype field i am getting the subtype code in the table. I tried to use the Decode function but i did not able to use properly due to my little knowledge on Arcade data expr4ession behavior in Dashboard. Please see the code below. I also tried to use dictionary but i could not add properly i believe.
I came to see many helpful posts by @jcarlson but i could not able to implement !
Also I want to get rid of the null records. I would love to hear any comments /solutions on this. Thanks in Advanced! 😊
// STEP : 1
var p = Portal("https://arcgis.com");
var fs = FeatureSetByPortalItem(
p,
"4ee2810f5ff947338c9e10b660462c1f",
0,
["Fitting_Type_DMZ", "CreationDate", "Surveyed_By"],
false
);
// return fs;
// returns a meaningful value when a field contains coded values
var code = fs.Fitting_Type_DMZ;
var decodedValue = Decode(
code,
2,"Air Valve",
4, "Gate Valve",
6, "Flowmeter",
9, "Zone Valve",
12, "Boundary Valve",
"Unknown");
return GroupBy(fs, ["Fitting_Type_DMZ"],
[{name:"Counts",
expression:"Fitting_Type_DMZ", statistic:"COUNT"}]
);
Solved! Go to Solution.
You can do this with a SQL expression in your GroupBy function. This expression effectively recreates your Decode function, but in the GroupBy process itself.
var filt_fs = Filter(fs, 'Fitting_Type_DMZ is not null')
var type_sql = `CASE
WHEN Fitting_Type_DMZ = 2 THEN 'Air Valve'
WHEN Fitting_Type_DMZ = 4 THEN 'Gate Valve'
WHEN Fitting_Type_DMZ = 6 THEN 'Flowmeter'
WHEN Fitting_Type_DMZ = 9 THEN 'Zone Valve'
WHEN Fitting_Type_DMZ = 12 THEN 'Boundary Valve'
ELSE 'Unknown'
END`
return GroupBy(
fs,
{ name: 'Fitting_Type_DMZ', expression: type_sql },
[{ name: 'Counts', expression: 'Fitting_Type_DMZ', statistic: 'COUNT' }]
)
Note that calling the Decode function does not alter the original FeatureSet, it can only be used to create new variables or values.
I understand we can achieve this by using Table element. but I dont want to. because in the same list i will need to groupBy the Date (Without Time) and a filter "Today" so i can see the counts of survey for each type of fittings in the list.
You can do this with a SQL expression in your GroupBy function. This expression effectively recreates your Decode function, but in the GroupBy process itself.
var filt_fs = Filter(fs, 'Fitting_Type_DMZ is not null')
var type_sql = `CASE
WHEN Fitting_Type_DMZ = 2 THEN 'Air Valve'
WHEN Fitting_Type_DMZ = 4 THEN 'Gate Valve'
WHEN Fitting_Type_DMZ = 6 THEN 'Flowmeter'
WHEN Fitting_Type_DMZ = 9 THEN 'Zone Valve'
WHEN Fitting_Type_DMZ = 12 THEN 'Boundary Valve'
ELSE 'Unknown'
END`
return GroupBy(
fs,
{ name: 'Fitting_Type_DMZ', expression: type_sql },
[{ name: 'Counts', expression: 'Fitting_Type_DMZ', statistic: 'COUNT' }]
)
Note that calling the Decode function does not alter the original FeatureSet, it can only be used to create new variables or values.
Hi Josh,
This worked just fine for me. huge thanks !😊