[Help] Convert Excel Formula to Alteryx Formula
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello Everyone,
Working on Alteryx for the first time, but not an excuse for not being able to convert this to Alteryx Expression. I am somehow getting confused about how IF and IIF work in this tool. For now, I request you to help me convert the following Excel Formula to an Alteryx usable formula.
If the below formula is not clear to understand, I appreciate if you can provide me with an example on how I can write a NESTED IF expressions.
Thanks.
=if(
and(
value(Sheet1[ID])>=value(Sheet1[Record1]),
value(Sheet1[ID])<=if(isblank(Sheet1[Record2])=true,
value(Sheet1[Year])
),
value(Sheet1[ID_A2]),
if(isblank(Sheet1[Record2])=true,value(Sheet1[IDS_1]),value(Sheet1[IDS_3A]))
)
Solved! Go to Solution.
- Labels:
- Expression
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Arcane assuming you have all the values are numeric type, if not you need to apply toNumber() function
If [ID] >= [Record1] AND [ID] <= (IIF(IsEmpty([Record2]),[Year],[Record2])) Then [ID_A2] ElseIf IsEmpty([ID_A2]) Then [IDS_1]
else [IDS_3A]
EndIf
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks binuacs, your warning has helped me.
I have written the formula using IIF(...,.., IIF...) but I did not look at the Datatype of the columns I am referring to, in the condition which was throwing me an error.
I wish it had said me to check data types of the columns rather than saying IIF Malfunction. Much time has gone. but learned the hard way :)
