Dashboard - Multiple Arcade IIf Statments to assign a category to a range of values and filter data

[DoZ]

Dear Community,

I am trying to write a multiple Iif condition but not sure how to do it right.

I have a simple Table with IdCodes (integer) that correspond to Team Names. Each “Team_id” corresponds only to one “Team Name”.

Since there are lot of them (around 60 types of Team_id), I need to group them in order to use the result in a Selector widget in my Dashboard.

I want to tell de dashboard that he should filter the data based on the Team Category (see table but the Column “Team_category” doesn’t exist, this is what I need the Arcade expression t figure out).

If Team_id is greater than or equal to 1000 AND less than or equal to 1999 = CAT 1, but if the Team_id is greater than or equal to 2000 AND less than or equal to 2999 = CAT 2 and so on….for at least 9 or 10 times.

The expression should return the full of my original table + the new Field “Team_category” which I will use in a SELCTOR widget to filter data as “Grouped values”..

I found this answer but it is not quite what I need….

https://community.esri.com/t5/arcgis-online-questions/arcade-expression-to-find-text-in-string-and/t...

Any help? Thank you all!!!

😁🙏

Team_id	Teamn_name	Team_category
1000	Team A	CAT 1
1001	Team B	CAT 1
1200	Team C	CAT 1
2000	Team D	CAT 2
2001	Team E	CAT 2
2100	Team F	CAT 2
3000	Team G	CAT
3001	Team H	CAT 3
3700	Team I	CAT 3
…	….	…
9000	Team XYZ	CAT 9

[KenBuja]

You can use the GroupBy function with and SQL Case statement to do this

var fs = FeatureSetByPortalItem(
  Portal("your portal"),
  "your item",
  0,
  ["Team_id", "Teamn_name"],
  false
);

var sql =
  `CASE
    WHEN Team_id < 1000 THEN 'Cat 0'
    WHEN Team_id < 2000 THEN 'Cat 1'
    WHEN Team_id < 3000 THEN 'Cat 2'
    WHEN Team_id < 4000 THEN 'Cat 3'
    WHEN Team_id < 5000 THEN 'Cat 4'
    WHEN Team_id < 6000 THEN 'Cat 5'
    WHEN Team_id < 7000 THEN 'Cat 6'
    WHEN Team_id < 8000 THEN 'Cat 7'
    WHEN Team_id < 9000 THEN 'Cat 8'
    WHEN Team_id < 10000 THEN 'Cat 9'
  END`;

var TeamGroup = GroupBy(
  fs,
  ["Team_ID", "Teamn_name"],
  [
    { name: "TeamCategory", expression: sql, statistic: "MAX" }
  ]
);
return TeamGroup;

[DoZ]

Thank you @KenBuja, your solution worked but didn't bring me the whole Table back. Anyway is a good solution I will keep it in mind for other uses 🙂

Thank you!!

[jcarlson · Accepted solution]

So you're really just looking to add a new field that doesn't exist. The best way to do this, in my experience, is to use Distinct, but to have it return distinct values on the objectID. In this way, the resulting table is the same as your input table, plus any ad-hoc fields you include.

// get your featureset
var fs = FeatureSetByPortalItem(
  Portal('your portal url'),
  // portal item id
  "1234567890abcdef",
  0, // layer id
  ["*"], // fields to include
  false // include or exclude geometry
);

// convert field list to series of SQL expressions for Distinct function
var fields = Schema(fs)['fields']
var sqlfields = []

for (var f in fields) {
  Push(
    sqlfields,
    {name: fields[f]['name'], expression: fields[f]['name']}
  )
}

var teamcat_sql = `CASE
WHEN Team_id >= 1000 and Team_id < 2000 THEN 'CAT 1'
WHEN Team_id < 3000 THEN 'CAT 2'
WHEN Team_id < 4000 THEN 'CAT 3'
WHEN Team_id < 5000 THEN 'CAT 4'
WHEN Team_id < 6000 THEN 'CAT 5'
WHEN Team_id < 7000 THEN 'CAT 6'
WHEN Team_id < 8000 THEN 'CAT 7'
WHEN Team_id < 9000 THEN 'CAT 8'
WHEN Team_id < 10000 THEN 'CAT 9'
ELSE 'Other'
END`

return Distinct(
  fs,
  Splice(
    sqlfields,
    {name: 'Team_category', expression: teamcat_sql}
  )
)

Here's me testing it against a different layer, but showing the same concept. The "Team_category" field is based on the "ROW__ID" value.

[DoZ]

Sorry to bother you with one last question but how can I insert this DISTINCT function in an already existing expression?

My data source needs to have the Team_category value (which comes from the Distinct function) but also the sum of Holidays (new and old holiday) and the amount of total holiday (which is originally expressed in minutes and therefore needs to be divided by 60 to get the hours)

My Expression looks like this:

// get  featureset
var fs = FeatureSetByPortalItem(
  Portal('your portal url'),
  // portal item id
  "1234567890abcdef",
  0, // layer id
  ["*"], // fields to include
  false // include or exclude geometry
);

// Create empty feature array and feat object for output
var features = [];
var feat;
// Iterate fs

for (var feature in fs) {
        
    // Create feature with aggregated values
    feat = { 
        'attributes': { 
        'Team_id': feature['Team_id'],
        'Team': feature['Team'],
        'Name_Vorname': feature['Name_Vorname'],
	'Holiday_new': feature['Holiday_new']/60,
	'Holiday_old': feature['Holiday_old']/60,
	'Holiday_TOT': feature(['Holiday_old'] + ['Holiday_new'])/60
            }
    };
    
    // Push feature into array
    Push(features, feat);
};


// Create dict for output FeatureSet
var out_dict = { 
    'fields': [
        {'name': 'Team_id', 'type': 'esriFieldTypeInteger'},
        {'name': 'Team', 'type': 'esriFieldTypeString'},
        {'name': 'Name_Vorname', 'type': 'esriFieldTypeString'},
	{'name': 'Holiday_new', 'type': 'esriFieldTypeDouble'},
	{'name': 'Holiday_old', 'type': 'esriFieldTypeDouble'},
	{'name': 'Holiday_TOT', 'type': 'esriFieldTypeDouble'}, 
    ],
  'geometryType': '', 
  'features': features 
}; 

// Convert dictionary to feature set. 
//return FeatureSet(Text(out_dict));

var fields = Schema(fs)['fields']
var sqlfields = []

for (var f in fields) {
  Push(
    sqlfields,
    {name: fields[f]['name'], expression: fields[f]['name']}
  )
}

var teamcat_sql = `CASE
WHEN Team_id >= 1000 and Team_id < 2000 THEN 'CAT 1'
WHEN Team_id < 3000 THEN 'CAT 2'
WHEN Team_id < 4000 THEN 'CAT 3'
WHEN Team_id < 5000 THEN 'CAT 4'
WHEN Team_id < 6000 THEN 'CAT 5'
WHEN Team_id < 7000 THEN 'CAT 6'
WHEN Team_id < 8000 THEN 'CAT 7'
WHEN Team_id < 9000 THEN 'CAT 8'
WHEN Team_id < 10000 THEN 'CAT 9'
ELSE 'Other'
END`

return Distinct(
  fs,
  Splice(
    sqlfields,
    {name: 'Team_category', expression: teamcat_sql}
  )
)

I have tried to replace the word fs in var fields = Schema(fs)['fields'], with features or feat or out_dict thinking that would do it, but as usual I do not get very far..😑

Thank you soo much and kindest regards,

Dory

[jcarlson · Accepted solution]

In your example, you've got a return at line 50, so it's going to return the featureset before the Distinct ever happens.

To get those other fields in, we just need to add them to the Splice. They're simple enough that the SQL expression is very similar to what you already have.

return Distinct(
  fs,
  Splice(
    sqlfields,
    {name: 'Team_category', expression: teamcat_sql},
    {name: 'Holiday_new', expression: "Holiday_new / 60"},
    {name: 'Holiday_old', expresion: "Holiday_old / 60"},
    {name: 'Holiday_TOT', expression: "(Holiday_old + Holiday_new) / 60"}
  )
)

I should add: since we're using Distinct, you don't need to create the out_dict object or loop through your features.

[DoZ]

Perfect! It worked amazingly! Thank you sooo much!

Best regards

Dory

[DoZ]

Dear  @jcarlson 

your solution worked very well, thanks it was exactly what I was looking for!

Thanks!!