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

ダッシュボード - 複数の Arcade IIf 文を使って値の範囲にカテゴリを割り当て、データをフィルターする

DoZ

コミュニティの皆さん、こんにちは。

複数の Iif 条件を書こうとしているのですが、正しい書き方がわかりません。

Team Name に対応する IdCodes（整数）を含むシンプルなテーブルがあります。それぞれの「Team_id」に対応する「Team Name」は1つだけです。

数が多いので（Team_id は約60種類）、ダッシュボードのセレクターウィジェットで結果を使えるように、グループ化する必要があります。

Team Category に基づいてデータをフィルターするよう、ダッシュボードに指定したいです（テーブルを見てください。ただし「Team_category」列は存在しません。これを算出する Arcade 式が必要です）。

Team_id が1000以上1999以下なら CAT 1、2000以上2999以下なら CAT 2、という条件を少なくとも9～10回繰り返します。

式は元のテーブル全体に新しいフィールド「Team_category」を追加して返す必要があります。このフィールドを SELCTOR ウィジェットで「グループ化された値」として使い、データをフィルターします。

こちらの回答を見つけましたが、求めているものとは少し違うようです……

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

何か助言をいただけませんか？皆さん、ありがとうございます！！！

😁🙏

Team_id	チーム名	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

タグ付きの投稿をもっと見る

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

会話に参加する

サインインしたメンバーは投稿、更新のフォローなどができます。初めてですか？無料アカウントを登録してください。

クイックリンク

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