[Help] Convert Excel Formula to Alteryx Formula

[Arcane]

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]))
)

[NikyN]

Hello @Arcane,

could you please provide excel with formula?

Best Luck!

NN

[binuacs · Accepted solution]

@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

[Arcane]

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 :)