If then statement for a calculated value.

409
7
Jump to solution
02-26-2021 01:08 AM
GarrettBerge
New Contributor

I'm trying to convert wind direction from degrees to compass. I have a calculated wind direction value, but I want to be able to classify it as compass direction instead of the degree as a field in Survey123. These are the compass categories I would like the value to fall into. I know I have to write an if-then statement in the calculation column, but I am having a heck of a time wrapping my head around the syntax. Please help!

  • N = North (349 - 011 degrees)
  • NNE = North-Northeast (012-033 degrees)
  • NE = Northeast (034-056 degrees)
  • ENE = East-Northeast (057-078 degrees)
  • E = East (079-101 degrees)
  • ESE = East-Southeast (102-123 degrees)
  • SE = Southeast (124-146 degrees)
  • SSE = South-Southeast (147-168 degrees)
  • S = South (169-191 degrees)
  • SSW = South-Southwest (192-213 degrees)
  • SW = Southwest (214-236 degrees)
  • WSW = West-Southwest (237-258 degrees)
  • W = West (259-281 degrees)
  • WNW = West-Northwest (282-303 degrees)
  • NW = Northwest (304-326 degrees)
  • NNW = North-Northwest (327-348 degrees)
0 Kudos
1 Solution

Accepted Solutions
jcarlson
Regular Contributor II

It would be an absolute bear of a calculation formula to nest all those ifs, but it's doable. I would first want to know what the intended end use of the classified "direction" attribute is, though. It might be possible to have this as an expression evaluating on the fly using Arcade, depending on where you're using the information.

But to do it in Survey123, you just need to keep track of your parenthesis. Here's part of the code broken up and formatted nicely so you can see what's going on a little easier. Basically, just write each range in its own if, then at the very end include a '' for the final "else", then a close parenthesis for each if statement.

Also, since N spans the 0-line, its statement looks different from the rest

 

 

if(${bearing} < 12) or ${bearing} >= 349, 'N',
if(${bearing} >=12 and ${bearing} < 34, 'NNE',
if(${bearing} >=34 and ${bearing} < 57, 'NE',
if(${bearing} >= 57 and ${bearing} < 79, 'ENE',
''))))

 

 

 In Survey, this'll need to be all on one line, but it should work.

jcarlson_0-1614347892617.png

Alternate Approach: Concatenate Separate Ifs

It occurs to me that there is a way to cut the number of statements in half. If we were to draw out the compass, it might look like this:

jcarlson_1-1614348939323.png

If we evaluate the major cardinal directions separately, then concatenate them to an evaluation of the diagonals, we can get all 16 possible combinations with only 8 ifs. I won't type them all out, but here's a brief portion to give you an idea.

 

concat(
    if(${bearing} <= 33 or ${bearing} >= 327, 'N',
    if(${bearing} >= 57 and ${bearing} <= 123, 'E',
    '')),
    if(${bearing} >= 12 and ${bearing} <= 78, 'NE',
    if(${bearing} >= 102 and ${bearing} <= 168, 'SE',
    ''))
)

 

 In those areas where the major direction "wedge" intersects with the diagonal "wedge", you'll end up with values like "NNE" or "WSW". In areas where they do not, the empty string will be returned from one or the other if statement, and you'll get "N" or "SE".

- Josh Carlson
Kendall County GIS

View solution in original post

7 Replies
jcarlson
Regular Contributor II

It would be an absolute bear of a calculation formula to nest all those ifs, but it's doable. I would first want to know what the intended end use of the classified "direction" attribute is, though. It might be possible to have this as an expression evaluating on the fly using Arcade, depending on where you're using the information.

But to do it in Survey123, you just need to keep track of your parenthesis. Here's part of the code broken up and formatted nicely so you can see what's going on a little easier. Basically, just write each range in its own if, then at the very end include a '' for the final "else", then a close parenthesis for each if statement.

Also, since N spans the 0-line, its statement looks different from the rest

 

 

if(${bearing} < 12) or ${bearing} >= 349, 'N',
if(${bearing} >=12 and ${bearing} < 34, 'NNE',
if(${bearing} >=34 and ${bearing} < 57, 'NE',
if(${bearing} >= 57 and ${bearing} < 79, 'ENE',
''))))

 

 

 In Survey, this'll need to be all on one line, but it should work.

jcarlson_0-1614347892617.png

Alternate Approach: Concatenate Separate Ifs

It occurs to me that there is a way to cut the number of statements in half. If we were to draw out the compass, it might look like this:

jcarlson_1-1614348939323.png

If we evaluate the major cardinal directions separately, then concatenate them to an evaluation of the diagonals, we can get all 16 possible combinations with only 8 ifs. I won't type them all out, but here's a brief portion to give you an idea.

 

concat(
    if(${bearing} <= 33 or ${bearing} >= 327, 'N',
    if(${bearing} >= 57 and ${bearing} <= 123, 'E',
    '')),
    if(${bearing} >= 12 and ${bearing} <= 78, 'NE',
    if(${bearing} >= 102 and ${bearing} <= 168, 'SE',
    ''))
)

 

 In those areas where the major direction "wedge" intersects with the diagonal "wedge", you'll end up with values like "NNE" or "WSW". In areas where they do not, the empty string will be returned from one or the other if statement, and you'll get "N" or "SE".

- Josh Carlson
Kendall County GIS

View solution in original post

GarrettBerge
New Contributor

Thank you so much!

0 Kudos
JamesTedrick
Esri Esteemed Contributor

Hi @GarrettBerge 

@jcarlson's solutions are very good.  I might suggest 1 small tweak:

You should only need less than/less than or equal comparaisons for the first (16 if statements) solution; you can account for the wrap (large values being the same as smaller ones) by using the same value as the final 'if false' value.  As an example for the 4 directions (the splits at 45 degrees):

 

if(${bearing} <= 45, 'N',
    if(${bearing} <= 135, 'E',
        if(${bearing} <= 225, 'S',
            if(${bearing} <= 315, 'W','N')
        )
    )
)

 

 

ChrisR_
New Contributor

James, I have a like challenge, and I have tried to follow the example, but I keep getting denied.  Can you take a look and let me know what is askew? 

I am working in s123 arcgis connect > xlsx file > calculate field.

 

if(${total_points} <= 18.5, 'Ephemeral',

if(${total_points} >= 18.5 and ${total_points}<= 29.5, 'Intermittent',

if(${total_points} >= 30), 'Perennial')))

 

I have attached the xlsx just in case, record 19 is where I am trying to plug in the formula.

 

Appreciate the consideration.

 

Cheers, Will

0 Kudos
DougBrowning
MVP Frequent Contributor

Another idea is to have a csv with rows 0-360 then use pulldata to go get the value.  Easier to write and easy to make with excel autofill.

0 Kudos
DougBrowning
MVP Frequent Contributor

You missed the last else.  

if(${total_points} <= 18.5, 'Ephemeral',

if(${total_points} >= 18.5 and ${total_points}<= 29.5, 'Intermittent',

if(${total_points} >= 30), 'Perennial', "Missing this one')))

But If you want all values > 30 on the last one then no need for the last if though anyway

if(${total_points} <= 18.5, 'Ephemeral', if(${total_points} >= 18.5 and ${total_points} <= 29.5, 'Intermittent', 'Perennial'))

ChrisR_
New Contributor

very much thanks, your kung fu is unbeatable!

 

shalom, will