nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Tableau de bord - Plusieurs instructions Arcade IIf pour attribuer une catégorie à une plage de valeurs et filtrer les données

DoZ

Bonjour à la communauté,

J’essaie d’écrire une condition Iif multiple, mais je ne sais pas trop comment m’y prendre.

J’ai une table simple avec des codes d’identification (entiers) correspondant aux noms d’équipe. Chaque « Team_id » correspond à un seul « nom d’équipe ».

Comme il y en a beaucoup (environ 60 types de Team_id), je dois les regrouper afin d’utiliser le résultat dans un widget Sélecteur de mon tableau de bord.

Je veux indiquer au tableau de bord qu’il doit filtrer les données en fonction de la catégorie d’équipe (voir le tableau, mais la colonne « Team_category » n’existe pas : c’est ce que je cherche à déterminer avec l’expression Arcade).

Si Team_id est supérieur ou égal à 1000 ET inférieur ou égal à 1999 = CAT 1, mais si Team_id est supérieur ou égal à 2000 ET inférieur ou égal à 2999 = CAT 2, et ainsi de suite… au moins 9 ou 10 fois.

L’expression doit renvoyer l’intégralité de ma table d’origine, ainsi que le nouveau champ « Team_category », que j’utiliserai dans un widget SÉLECTEUR pour filtrer les données par « valeurs regroupées »..

J’ai trouvé cette réponse, mais ce n’est pas tout à fait ce qu’il me faut…

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

Quelqu’un peut m’aider ? Merci à tous !!!

😁🙏

Team_id	Nom de l’équipe	Team_category
1000	Équipe A	CAT 1
1001	Équipe B	CAT 1
1200	Équipe C	CAT 1
2000	Équipe D	CAT 2
2001	Équipe E	CAT 2
2100	Équipe F	CAT 2
3000	Équipe G	CAT
3001	Équipe H	CAT 3
3700	Équipe I	CAT 3
…	….	…
9000	Équipe XYZ	CAT 9

Trouver plus de publications taguées avec

Arcade

Accepted answers

jcarlson

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.

jcarlson

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.

All comments

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;

jcarlson

// 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

Dear @jcarlson

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

Thanks!!

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!!

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

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

Rejoindre la conversation

Les membres connectés peuvent publier, suivre les mises à jour, et plus encore. Nouveau ici ? Inscrivez-vous gratuitement.

Liens rapides

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.