IF less than/greater than statemtent

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

Community

Accepted answers

Yoshiro_Fujimori

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)

1313560.yxmd

All comments

Yoshiro_Fujimori

@Lizbhernan ,

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.

KGT

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.

Lizbhernan

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.

Adjustments Workflow.yxmd

Lizbhernan

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)

Adjustments Workflow.yxmd

KGT

@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:

!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