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

Dashboard - Meerdere Arcade IIf-statements om een categorie toe te wijzen aan een reeks waarden en gegevens te filteren

DoZ

Beste community,

Ik probeer een voorwaarde met meerdere Iif-statements te schrijven, maar weet niet zeker hoe ik dat goed moet doen.

Ik heb een eenvoudige tabel met IdCodes (geheel getal) die overeenkomen met teamnamen. Elke “Team_id” komt overeen met slechts één “Team Name”.

Omdat het er veel zijn (ongeveer 60 typen Team_id), moet ik ze groeperen om het resultaat te kunnen gebruiken in een Selector-widget in mijn dashboard.

Ik wil het dashboard laten weten dat het de gegevens moet filteren op basis van de teamcategorie (zie de tabel, maar de kolom “Team_category” bestaat niet; dat is wat de Arcade-expressie moet bepalen).

Als Team_id groter dan of gelijk aan 1000 EN kleiner dan of gelijk aan 1999 is = CAT 1, maar als Team_id groter dan of gelijk aan 2000 EN kleiner dan of gelijk aan 2999 is = CAT 2, enzovoort… voor minstens 9 of 10 keer.

De expressie moet de volledige oorspronkelijke tabel plus het nieuwe veld “Team_category” retourneren, dat ik in een SELECTOR-widget zal gebruiken om gegevens te filteren als “Grouped values”..

Ik heb dit antwoord gevonden, maar het is niet helemaal wat ik nodig heb….

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

Kan iemand helpen? Alvast bedankt!!!

😁🙏

Team_id	Teamnaam	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

Vind meer berichten getagd met

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

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

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