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
Solved! Go to Solution.
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;
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.
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.
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)
@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.
A couple of comments:
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)
Thank you both for your help!