Using the Names of Subtypes in Dashboard List - GroupBy data expression (Arcade)

[AfdalCahaya]

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"}]
);

[AfdalCahaya]

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.

[jcarlson · Accepted 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.

[AfdalCahaya]

Hi Josh, 

This worked just fine for me. huge thanks !😊