IF less than/greater than statemtent

[Lizbhernan]

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 

[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.

[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)

[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

[Yoshiro_Fujimori · Accepted solution]

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)

[Lizbhernan]

Thank you both for your help!