In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

[Help] Convert Excel Formula to Alteryx Formula

Arcane
7 - Meteor

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

3 REPLIES 3
NikyN
9 - Comet

Hello @Arcane,

 

could you please provide excel with formula?

 

Best Luck!

NN

binuacs
21 - Polaris

@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
7 - Meteor

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

Labels
Top Solution Authors