CONVERT EXCEL NESTED IF TO ALTERYX

I am new to Alteryx and trying to convert this Excel formula into Alteryx using the multi row formula tool with no luck. Any assistance would be appreciated.

=IF(A2<>A1,IF((M2+K2)<=0,M2,IF((K2+M2)>0,K2*-1,0)),IF((K2+N2)<=0,IF((K2+N2)>0,K2*-1,N2),-K2))

Sample Data.xlsx

Expression

Accepted answers

Ben_H

Hi @Caclark ,

Using the IIF function (rather than IF THEN formatting) makes this fairly straightforward as it operates the same way as the Excel IF statement.

To cover the first statement that checks whether the row above is different make sure to use a multi-row formula tool.

IIF([Payor ID]!=[Row-1:Payor ID],
IIF(([PotentialRefund]+[RESP_AMT])<=0,[PotentialRefund],
IIF(([PotentialRefund]+[RESP_AMT])>0,[RESP_AMT]*-1,0)),
IIF(([RefundRemaining]+[RESP_AMT])<=0,
IIF(([RefundRemaining]+[RESP_AMT])>0,[RESP_AMT]*-1,[RefundRemaining])
,-[RESP_AMT]))

Regards,

Ben

All comments

hanykowska

Hi @Caclark

First step for me is to unpick the excel formula into something easier to read

Before:

=IF(A2<>A1,IF((M2+K2)<=0,M2,IF((K2+M2)>0,K2*-1,0)),IF((K2+N2)<=0,IF((K2+N2)>0,K2*-1,N2),-K2))

Step 1:

=IF(A2<>A1,

IF( (M2+K2)<=0,

M2,

IF((K2+M2)>0,

K2*-1,

IF((K2+N2)<=0,

IF((K2+N2)>0,

K2*-1,

-K2)

)

Step 2: change it to alteryx logic - let's assume this formula is for row 2 (less typing for now but could be adjusted)

IF [A] != [Row-1:A]

THEN

IF [M] + [K] <= 0

THEN [M]

ELSEIF [M] + [K] > 0

THEN [K] * (-1)

ELSE 0

ENDIF

ELSEIF [K] + [N] <= 0

THEN [N]

ELSEIF [K] + [N] > 0

THEN [K] * (-1)

ELSE -[K]

ENDIF

This logic is quite convoluted so I tried to simplify it as I went, although you could try and simplify it even more.

IF((K2+N2)<=0,

IF((K2+N2)>0,

K2*-1,

-K2)

You will never reach the highlighted section because if you pass K2+N2<=0 you will not pass K2+N2>0

further, the following section

ELSEIF [K] + [N] > 0
THEN [K] * (-1)
ELSE -[K]

could be simplified to ELSE -[K]

Step 3: replace references to columns to the field names
(these might be wrong so you should double-check they are correct)

A - Payor ID

K - RESP_AMT

M - PotentialRefund

N - PotentialRemaining

It would be great if you could share the values you expect for the provided data

excel formula.yxzp

Ben_H

Hi @Caclark ,

Using the IIF function (rather than IF THEN formatting) makes this fairly straightforward as it operates the same way as the Excel IF statement.

To cover the first statement that checks whether the row above is different make sure to use a multi-row formula tool.

Regards,

Ben

atcodedog05

Hi @Ben_H

This is a great method.

I think it is suggestable that we should all think about Alteryx IIF which is similar to excel If. Which will be great and fast formula replication.

Excel : if(condition,True,False)

Alteryx : iff(condition,True,False)

Just do replace all on IF to IIF. Brilliant idea 😎

Thanks a lot for approach and learning 🙂

grazitti_sapna

Hi @Caclark , give this a try if it works for you.

If this is what you are looking for kindly mark this post as solution and give a like.

Thanks.

Excel_Nested_If-GS.yxmd

hanykowska

This is a great way to quickly implement the same logic in alteryx, @Ben_H !

Really nice.

One thing I found is that in some cases the logic in excel is not necessarily the best. For a quick implementation, I would go with your IIF approach, but then I would take some more time to untangle the logic and see if:

1) it makes sense

2) it's efficient

as when I was unpicking the logic presented by @Caclark I found some issues that I would try to straighten out before marking the workflow/project complete

Caclark

Thank You, this worked!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703