IF AND OR Statement - Arcade

988
11
Jump to solution
08-04-2020 03:21 PM
MVP Regular Contributor

I can write what I am trying to do in excel but not in Arcade.

I am looking for a way to write something like this:

I have an IF statement followed by an AND with a bunch of OR statements. 

=IF(AND(OR(K4=1,K4=4),OR(L4=2,L4=1),OR(M4=2,M4=1),OR(N4=1,N4=4),OR(O4=2,O4=1),OR(P4=2,P4=1)),"0",IF(AND(OR(K4=2,K4=3,K4=5),L4=3,M4=3),"250","150"))'
Reply
0 Kudos
1 Solution

Accepted Solutions
MVP Honored Contributor

I tried this in the Arcade playground and it returns 0. Changing the three numbers as commented will return 250, but changing only one number will return 150

var fveg_z1=1; //5 These three values yields 250
var sveg_z1=2; //3 These three values yields 250
var lad_fl1=2; //3 These three values yields 250
var fveg_z2=1;
var sveg_z2=2;
var lad_fuel_2=2;

var result = IIf((fveg_z1==1 || fveg_z1==4) &&
(sveg_z1==2 || sveg_z1==1) &&
(lad_fl1==2 || lad_fl1==1) &&
(fveg_z2==1 || fveg_z2==4) &&
(sveg_z2==2 || sveg_z2==1) &&
(lad_fuel_2==2 || lad_fuel_2==1), "0",
IIf((fveg_z1==2 || fveg_z1==3 || fveg_z1==5) &&
sveg_z1==3 &&
lad_fl1==3, "250",
"150")
);
return result;

What are the actual values for those fields?

View solution in original post

11 Replies
Esri Esteemed Contributor

Hi dafiter ,

See below a translation of the Excel formula into Arcade and the first lines to specify values for testing:

var k = 2; // 1-5
var l = 3; // 1-3
var m = 3; // 1-3
var n = 1; // 1,4
var o = 1; // 1,2
var p = 1; // 1,2

var result = IIf((K==1 || K==4) && (L==2 || L==1) && (M==2 || M==1) && (N==1 || N==4) && (O==2 || O==1) && (P==2 || P==1), "0", IIf((K==2 || K==3 || K==5) && L==3 && M==3, "250","150"));
return result;
MVP Regular Contributor

Xander Bakker

Thank you for helping me with this.

Here is the code after I plug in my fields.

var result = IIf(($feature.fveg_z1==1 || $feature.fveg_z1==4) && ($feature.sveg_z1==2 || $feature.sveg_z1==1) && ($feature.lad_fl1==2 || $feature.lad_fl1==1) && ($feature.fveg_z2==1 || $feature.fveg_z2==4) && ($feature.sveg_z2==2 || $feature.sveg_z2==1) && ($feature.lad_fuel_2==2 || $feature.lad_fuel_2==1), "0", IIf(($feature.fveg_z1==2 || $feature.fveg_z1==3 || $feature.fveg_z1==5) && $feature.sveg_z1==3 && $feature.lad_fl1==3, "250","150"));
return result;

All I get for answers is 150. I know that some should be 0 or 250.

Did I plug it in wrong? 

Reply
0 Kudos
MVP Honored Contributor

I tried this in the Arcade playground and it returns 0. Changing the three numbers as commented will return 250, but changing only one number will return 150

var fveg_z1=1; //5 These three values yields 250
var sveg_z1=2; //3 These three values yields 250
var lad_fl1=2; //3 These three values yields 250
var fveg_z2=1;
var sveg_z2=2;
var lad_fuel_2=2;

var result = IIf((fveg_z1==1 || fveg_z1==4) &&
(sveg_z1==2 || sveg_z1==1) &&
(lad_fl1==2 || lad_fl1==1) &&
(fveg_z2==1 || fveg_z2==4) &&
(sveg_z2==2 || sveg_z2==1) &&
(lad_fuel_2==2 || lad_fuel_2==1), "0",
IIf((fveg_z1==2 || fveg_z1==3 || fveg_z1==5) &&
sveg_z1==3 &&
lad_fl1==3, "250",
"150")
);
return result;

What are the actual values for those fields?

View solution in original post

MVP Regular Contributor

Ken Buja

Here is a sample of the data 

Reply
0 Kudos
MVP Honored Contributor

Since for this subset has fveg_z1=3, you're only going to get either 250 or 150. But none of the sveg_z1 value is 3, so all of these records will return 150

Reply
0 Kudos
MVP Regular Contributor

Ken Buja

Sorry that was just a screen shot. There are over 6000 records with all combinations in each field. 

Reply
0 Kudos
MVP Honored Contributor

Have you run a selection or filter on the data to verify the different combinations to get 0 or 250?

Reply
0 Kudos
MVP Regular Contributor

Ken Buja‌   

I have only ran it against the whole database. I will try a small subset and see what I get 

Update:

I just ran against what I would expect only 0 scores and I still get 150

Reply
0 Kudos
MVP Regular Contributor

Ken Buja

Does it matter if the fields sveg_z1 etc are text not long? 

Reply
0 Kudos