IF Statements

[Lizbhernan]

I am still new to Alteryx and IF statements have been killing me!

I am trying to write out IF override hours = blank then current hours * earning exclusion else override hours

In Excel it looks like this =IF([@[Override Hours]]="",[@[Current Hours]]*[@[Earning Exclusion]],[@[Override Hours]])

I tried IF [Override Hours_]=0 THEN [Hours] * [Earning Vacation Exclusions] else [Override Hours_] in Alteryx but I am not getting any results

I also tried IF [Override Hours_]=blank THEN [Hours] * [Earning Vacation Exclusions] else [Override Hours_] but that doesn't work at all

I know this should be simple but would appreciate any pointers.

Also any tips on things that can help with IF statements for beginners in Alteryx?

TIA

[CoG]

Have you remembered your "ENDIF".

To more closely follow the Excel formulation, you can use the IIF() function (with 2 i 's)

Also, what data types are you working with? For numeric data, your first formula should work, assuming you have ENDIF.

"blank" is not a keyword in Alteryx - to check for "blank" data you have the following checks:

• [Field] = "" (for Text Types) or [Field] = 0 (for Numeric Types)
• IsNull([Field])
• IsEmpty([Field]) (really only useful for Text Type as it checks for both IsNull() and = ""; returning true in either case)

Welcome to Alteryx, hope this helps and Happy Solving!

[Qiu]

@Lizbhernan 
As @CoG pointed out, "BLANK" Null, and Empty cell are different things in Alteryx 😁 

[Lizbhernan]

I am mostly working with numeric data, however, the Earning Vacation Exclusions is a True/False (v_string) but overall it should give me a Numeric answer.

I updated the formula to:

IIF([Override Hours_]=0 THEN [Hours] * [Earning Vacation Exclusions] else [Override Hours_]ENDIF) but I am getting an error. It says "Parse Error at char(24): Malformed If Statement (Expression#1)

I tried changing the Data type as I assumed that might be the error but it hasn't worked.

Not sure if maybe I am writing the formula incorrectly.

[CoG · Accepted solution]

It seems that I have confused you. There are 2 different formulations for conditional logic in Alteryx:

1. IF [condition] THEN [Case] ELSE [Case] ENDIF
2. IIF([condition],[Case],[Case])

#1 is more of a programmatic if statement and #2 is the excel adjacent. Your error is occurring because you have tried to merge both together. Given your code, here are both formulations:

1.  IF [Override Hours_]=0 THEN [Hours] * [Earning Vacation Exclusions] ELSE[Override Hours_] ENDIF
2. IIF([Override Hours_]=0, [Hours] * [Earning Vacation Exclusions], [Override Hours_])

Hopefully this clears up any confusion. It's a good demonstration for the many ways one may approach problem solving in Alteryx.

For myself, I tend to favor formulation #1 for more complex conditionals where I want to see each component separately:

IF [Override Hours_]=0

THEN [Hours] * [Earning Vacation Exclusions]

ELSE [Override Hours_]

ENDIF

Whereas I will use IIF() in cases where the logic is very simple (Coalescing for example: IIF(IsNull([Field]),0,[Field]))

Happy Solving!

[Lizbhernan]

Thank you for clarifying, that was helpful.

I updated the formula, however, I am still getting an error. The Earning Vacation Exclusion column is either True or False so it is not numeric data and cant necessarily be multiplied it by the hours. I again, tried to change the Data type but it is still not working.

Does this mean that this formula will not workout in Alteryx or what do you think I am doing wrong.

Thank you for your assistance and patience 

[Bren_Spill]

@Lizbhernan - change the Earning Vacation Exclusion data type to Bool and it should work, assuming the field currently contains True and False. This will convert True False to 1 and 0

See attached.

[Lizbhernan]

Thank you @Bren_Spill changing the Earning Vacation Exclusion data type to Bool worked.

@CoG Thank you so much for your help! 😀