Nested If

[danishrizvi]

Hello,

I have 3 columns - "Hours" (string) and "Login_Time" (time) and "Logout_Time"(time).

I want to get an outcome that if "Login_Time" is more than 08:00:00 then I should get "Late Login". If "Logout_Time" is blank then "No Logout" and if "Hours" are less than 9 then it should show "Early Logout" and if "Hours" are more than 12 then it should show "Incorrect Logout" and if none of the above then "-".

Please also confirm whether I will need to change the formats (string)(time) for any of the columns for the calculations to work? Appreciate your help.

[BrandonB]

In Alteryx, there is the traditional if statement:

IIF(evaluation statement, true value, false value)

But there is also a conditional statement that I think is a lot cleaner than using nested IIF statements

IF evaluation statement

THEN value

ELSEIF second evaluation statement

THEN value

ELSEIF third evaluation statement

THEN value

ELSE value

ENDIF

[fmvizcaino · Accepted solution]

Hi @danishrizvi ,

Attached in one example showing how to accomplish it.

The only thing you need to change to make a cleaner if statement is to change the hours to integer or double if decimal.

Let me know if that works for you.

Best,

Fernando Vizcaino

[danilang]

hi @danishrizvi 

What about multiple conditions, i.e. Late Login and Early Logout?

Dan

[danishrizvi]

Thank you so much. I just had to tweak the first part of the formula

from:

if DateTimeDiff('08:00:00',[Login_time],'minutes')>0
then 'Late Login'

to:

if [Login_time]> '08:00:00'
then 'Late Login'.

Worked perfect.Really appreciate your help on the full formula. Thank you.

[danishrizvi]

Another question on the nested if.

I have a column that has null/blank values, and "null" itself as a value, and "N/A" as a value, and "Not Applicable" as a value, and "none" as a value. I want to convert all these to a single value "N/A" and anything else in that column is to stay as is.

example formula I am trying to use is - 

IF IsNull([Column Name]) THEN "N/A" ELSEIF "Not Applicable" THEN "N/A" ELSEIF "null" THEN "N/A" ELSEIF "none" THEN "N/A" ELSE [Column Name] ENDIF.

However the above formula just converts the nulls/blanks to "N/A" and does not change the others ("Not Applicable", "none", "null") values to "N/A". Help

[BrandonB · Accepted solution]

You are missing the column name in your elseif statements

IF IsNull([Column Name])

THEN "N/A"

ELSEIF [Column Name] = "Not Applicable"

THEN "N/A"

ELSEIF [Column Name] = "null"

THEN "N/A"

ELSEIF [Column Name] = "none"

THEN "N/A"

ELSE [Column Name]

ENDIF.

[danishrizvi]

@BrandonB Thanks a ton. Much appreciate.