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; } } }

Painel — várias instruções Arcade IIf para atribuir uma categoria a um intervalo de valores e filtrar dados

DoZ

Olá, comunidade,

Estou tentando escrever uma condição com várias instruções IIf, mas não tenho certeza de como fazer isso corretamente.

Tenho uma tabela simples com IdCodes (inteiro) que correspondem aos nomes das equipes. Cada “Team_id” corresponde a apenas um “Team Name”.

Como há muitos deles (cerca de 60 tipos de Team_id), preciso agrupá-los para usar o resultado em um widget Seletor no meu painel.

Quero dizer ao painel que ele deve filtrar os dados com base na categoria da equipe (veja a tabela, mas a coluna “Team_category” não existe; é isso que preciso descobrir com a expressão Arcade).

Se Team_id for maior ou igual a 1000 E menor ou igual a 1999 = CAT 1, mas se Team_id for maior ou igual a 2000 E menor ou igual a 2999 = CAT 2, e assim por diante… pelo menos 9 ou 10 vezes.

A expressão deve retornar a tabela original completa + o novo campo “Team_category”, que usarei em um widget SELETOR para filtrar os dados como “Valores agrupados”..

Encontrei esta resposta, mas não é exatamente o que preciso….

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

Alguém pode ajudar? Agradeço a todos!!!

😁🙏

Team_id	Nome da equipe	Categoria da equipe
1000	Equipe A	CAT 1
1001	Equipe B	CAT 1
1200	Equipe C	CAT 1
2000	Equipe D	CAT 2
2001	Equipe E	CAT 2
2100	Equipe F	CAT 2
3000	Equipe G	CAT
3001	Equipe H	CAT 3
3700	Equipe I	CAT 3
…	….	…
9000	Equipe XYZ	CAT 9

Encontre mais publicações marcadas com

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

Participe da Conversa

Membros conectados podem postar, seguir atualizações e mais. Novo aqui? Registre uma conta gratuita.

Links Rápidos

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