alteryx Community

# General Discussions

Discuss any topics that are not product-specific here.
SOLVED

## IF less than/greater than statemtent

6 - Meteoroid

Good afternoon, I am fairly new to using Alteryx and could use some help.

I am trying to use the below formula (excel version) in Alteryx and I cant seem to figure it out. I tried the IF c THEN t ELSE f ENDIF formula but I don't think I'm using the correct formula.

Can someone help guide me as to what formula in Alteryx I can use?

=IF([@[H&W OR Amt]]<>"",[@[H&W OR Amt]],IF([@[H&W OR]]="",[@[H&W SR]],[@[H&W OR]])*[@Hours])

TYA

7 REPLIES 7
15 - Aurora

It would be easier to communicate your issue if you provide a sample input and expected output in table.

Your expression is arranged as below;

=IF([@[H&W OR Amt]]<>"",
[@[H&W OR Amt]],
IF([@[H&W OR]]="",
[@[H&W SR]],
[@[H&W OR]]
)*[@Hours]
)

From above, I understand your input and output as below.

 H&W OR Amt H&W OR H&W SR Hours Expected 1 2 3 4 1 3 4 12 2 3 4 8

If the above expexted values are what you want,

the below expression in Formula tool would work.

IF !IsEmpty([H&W OR Amt])
THEN [H&W OR Amt]
ELSE (
IF IsEmpty([H&W OR])
THEN [H&W SR]
ELSE [H&W OR]
ENDIF
) * [Hours]
ENDIF

I hope this helps.

11 - Bolide

That's a nested IF statement and so you could leave it as nested or switch to something easier to read like the below. Took me a minute to work out that [H&W OR Amt] is the name of the field not an OR statement... Excel is rusty.. I recommend checking my logic as I didn't test this and may have mucked up field names.

IF[@[H&W OR Amt]]<>"",

THEN [@[H&W OR Amt]],

ELSEIF([@[H&W OR]]="",

THEN [@[H&W SR]],

ELSE [@[H&W OR]])*[@Hours])

ENDIF

becomes:

IFIsEmpty([H&W OR Amt])

THEN [H&W OR Amt],

ELSEIF IsEmpty([H&W OR])

THEN [H&W SR]*[@Hours]

ELSE [H&W OR]*[@Hours]

ENDIF

If wanting to keep it nested, then the ELSEIF statement becomes ELSE IF.... instead of ELSEIF and then the entire part through to ENDIF must be nested under the ELSE.

6 - Meteoroid

Thank you Yoshiro.

I updated the formula and should read  =IF([@[H&W Override Amt2]]<>"",[@[H&W Override Amt2]],IF([@[H&W Override]]="",[@[Hourly Rate]],[@[H&W Override]])*[@Hours])

I attached the workflow I am working on for refence.

6 - Meteoroid

Ahh yes, the OR was confusing on the formula.

I updated it and attached the workflow I am working on to give more context

Formula should read as  =IF([@[H&W Override Amt2]]<>"",[@[H&W Override Amt2]],IF([@[H&W Override]]="",[@[Hourly Rate]],[@[H&W Override]])*[@Hours])

(I removed the OR to avoid any confusion)

11 - Bolide

@Yoshiro_Fujimori beat me with the nested solution! I'm a bit of a fan of that as it's easy to read if the indenting is set.

@Lizbhernan, either of those methods should work. I can't run your workflow as I don't have the input data, but using either mine or Yoshiro's formula should get you to your solution.

• !IsEmpty means "not empty" and will cover empty cells and null cells.
• Where in excel, "not equal to" is represented by <>, in Alteryx it is represented by !=
• Excel doesn't have the concept of empty vs null and so <>"" is commonly used in excel but doesn't translate directly to most data platforms. Hence the use of !IsEmpty
15 - Aurora

I attach a sample workflow for your reference.

(The input data and the formula are the same as my previous post.)

 H&W OR Amt H&W OR H&W SR Hours Expected Actual asExpected 1 2 3 4 1 1 TRUE 3 4 12 12 TRUE 2 3 4 8 8 TRUE

[Actual] =

IF !IsEmpty([H&W OR Amt])
THEN [H&W OR Amt]
ELSE (
IF IsEmpty([H&W OR])
THEN [H&W SR]
ELSE [H&W OR]
ENDIF
) * [Hours]
ENDIF

[asExpected] =

[Expected] = [Actual]

(type: Boolean)

6 - Meteoroid

Thank you both for your help!

Labels