[Help] Convert Excel Formula to Alteryx Formula

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

Workflow

Expression

Accepted answers

binu_acs

@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

All comments

NikyN

Hello @Arcane,

could you please provide excel with formula?

Best Luck!

binu_acs

@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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703