Malformed If Statement error

[czjames87]

Good afternoon,

I am trying to use the logic below but i keep getting a malformed if statement error. What am i missing? 

iif(IsNull([actual_ship_dt]),"Pending Delivery",iif(IsNull([delivery_appt_to_dt]),iif([scheduled_delivery_to_dt]<DateTimeToday(), "Delayed (non appt)",iif([apt_rqrd_yn] ="Y","Del Appt Required", iif([scheduled_delivery_to_dt] > DateTimeToday(), "Future Delivery","Status Pending")))

[DataNath]

A lot to unpack here, are you able to provide more detail/context? Perhaps some screenshots and/or sample inputs and desired outcomes?

[DavidSkaife]

Hi @czjames87 

Looking at the formula there are two issues:

After the 'iif(IsNull([delivery_appt_to_dt]),' you have no action if it's true, it just jumps straight into the next IFF

You're missing an additional two close brackets at the end to close off all five IFF statements

[binuacs · Accepted solution]

@czjames87 

IIF(IsNull([actual_ship_dt]),"Pending Delivery",
IIF(IsNull([delivery_appt_to_dt]),IIF([scheduled_delivery_to_dt]<DateTimeToday(), "Delayed (non appt)",
IIF([apt_rqrd_yn] ="Y","Del Appt Required", 
IIF([scheduled_delivery_to_dt] > DateTimeToday(), "Future Delivery","Status Pending")))))

[rfoster7 · Accepted solution]

rewritten out of iif syntax and into standard alteryx if elseif endif sytax: 

if isnull([actual_ship_dt]) then "Pending Delivery"
elseif isnull([deliver_appt_to_dt])
and [scheduled_delivery_to_dt] < datetimetoday() then "Delayed (non appt)"
elseif [apt_rqrd_yn] = "Y" then "Del Appt Required"
elseif [scheduled_delivery_to_dt] > datetimetoday() then "future delivery")
else "status pending"
endif

[alisonpitt · Accepted solution]

Further to what ^^ @DavidSkaife said, if you stack the formula up, it's a little easier to see:

Spoiler

[IraWatt · Accepted solution]

Hey @czjames87,

Two things you missed:

One you needed a true clause in your second if statement and secondly you needed 5 ending brackets )))))

iif(IsNull([actual_ship_dt]), "Pending Delivery",
iif(IsNull([delivery_appt_to_dt]), "",
iif([scheduled_delivery_to_dt]<DateTimeToday(), "Delayed (non appt)",
iif([apt_rqrd_yn] = "Y" , "Del Appt Required" , 
iif([scheduled_delivery_to_dt] > DateTimeToday(), "Future Delivery" , "Status Pending" )))))

HTH,

Ira

[czjames87]

thank you all for your help. All of the solutions provided worked. I appreciate the help. 

[IraWatt]

No worries @czjames87  ! :)