IF and Statement

[IanG001]

Hi all, I have a IFAND statement in excel I am trying to import to Ateryx, would anyone be able to help convert this?

=IF(AND(C2>=99999.99,E2=""),"High Value",IF(AND(C2>=99999.99,E2>=99999.99),"High Value",IF(AND(C2<1000000,E2>99999.99),"High Value")))

Thanks

Ian

[CharlieS · Accepted solution]

So here's a more or less direct translation (assuming you want to leave the value null/empty if not identified as "High Value")

IF C2>=99999 AND E2="" THEN "High Value"
ELSEIF C2>=99999.99 AND E2>=99999.99 THEN "High Value"
ELSEIF C2<1000000 AND E2>99999.99 THEN "High Value"
ELSE null() ENDIF

This could also be consolidated a bit since you have 3 "High Value" scenarios:

IF (C2>=99999 AND E2="")
OR (C2>=99999.99 AND E2>=99999.99)
OR (C2<1000000 AND E2>99999.99) THEN "High Value"
ELSE null() ENDIF

Also, don't forget that Alteryx will execute this formula on a record-by-record basis, so if you have the same field names of "C" and "E", the row numbers are basically built into the operation. Also, since these should be numeric fields, the better test for missing [E] values is isempty().

IF ([C]>=99999 AND isempty([E]))
OR ([C]>=99999.99 AND [E]>=99999.99)
OR ([C]<1000000 AND [E]>99999.99) THEN "High Value"
ELSE null() ENDIF

(The brackets technically aren't necessary, but they help illustrate the point and are good practice)

Check out the attached workflow for an example.

[IanG001]

Great, that really helpful, thanks again for your help.