# IF AND OR Statement - Arcade

988
11
08-04-2020 03:21 PM
by 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"))'
Tags (3)
1 Solution

Accepted Solutions
by 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 250var sveg_z1=2; //3 These three values yields 250var lad_fl1=2; //3 These three values yields 250var 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?

11 Replies
by 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-5var l = 3; // 1-3var m = 3; // 1-3var n = 1; // 1,4var o = 1; // 1,2var p = 1; // 1,2var 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;‍‍‍‍‍‍‍‍‍``
by MVP Regular Contributor

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?

by 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 250var sveg_z1=2; //3 These three values yields 250var lad_fl1=2; //3 These three values yields 250var 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?

by MVP Regular Contributor

Here is a sample of the data by 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

by MVP Regular Contributor

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

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

by MVP Regular Contributor

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

by MVP Regular Contributor

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