Hi,
I'm replacing a "horrible" Excel spreadsheet with a workflow, and although the formula is easy to understand in Excel I've not found a way to create it in Alteryx Designer...
AD3=IFERROR(IF(AB3="Limited","Limited","Fixed"),IFERROR(IF(AA3="Limited","Limited","Fixed"),AC3))
The IFERROR(...) formula have been added because column AB can contain #N/A "error values" as it was generated by an non-trapped error lookup
Column AA may also contain percentage numbers as well as N/A string values (and the #N/A error values).
Column AC, used as the default only contains valid strings or (blanks)
Both columns also contain the target "Limited" and "Fixed" strings too!
I'm sure there must be a way, but I can't see anything obvious...
Columns AA, AB and AC exist at the point in the workflow where I'm (hoping) to use a formula tool to add a new column AD.
Thanks in advance,
Paul
Solved! Go to Solution.
@Paul_s_Moody any chance you can put a snippet of the column(s) (or sample data) and the expected output? I'm sure we can build the logic!
@Paul_s_Moody Just got round to this. Two screenshots, one with your expected output, one with the output after using Alteryx formula:
1. Formula in Alteryx:
2. Your expected output
3. Side by side view
4. Formula to use for your column:
IF [GMP Revaluation Type] = "Limited" THEN "Limited"
ELSEIF [GMP Revaluation Type] = "#N/A" THEN
(
IF [GMPRevalRate] = "Limited" THEN "Limited"
ELSEIF [GMPRevalRate] = "#N/A" THEN [GMP Reval Rate]
ELSE "Fixed"
ENDIF
)
ELSE "Fixed"
ENDIF
I'll attach the workflow aswell. If it doesn't work, just @ me and we'll get it sorted.
Thank you!
It was the ability to embed an additional IF clause in (...) within the main clause that I was missing!
I'd not seen any examples of this so many thanks.
Knowing this will make other formula easier to deal with too!
Regards,
Paul