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")))
Solved! Go to Solution.
A lot to unpack here, are you able to provide more detail/context? Perhaps some screenshots and/or sample inputs and desired outcomes?
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
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")))))
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
Further to what ^^ @DavidSkaife said, if you stack the formula up, it's a little easier to see:
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
thank you all for your help. All of the solutions provided worked. I appreciate the help.
No worries @czjames87 ! :)