Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

CONVERT EXCEL NESTED IF TO ALTERYX

Caclark
5 - Atom

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

8 REPLIES 8
hanykowska
11 - Bolide

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,

0)

),

IF((K2+N2)<=0,

IF((K2+N2)>0,

K2*-1,

N2

),

-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.

In

IF((K2+N2)<=0,

IF((K2+N2)>0,

K2*-1,

N2

),

-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

image.png

Ben_H
11 - Bolide

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

atcodedog05
22 - Nova
22 - Nova

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
17 - Castor

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

grazitti_sapna_0-1602130319252.png

 

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

Thanks.

 

Sapna Gupta
hanykowska
11 - Bolide

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
5 - Atom

Thank You, this worked!

Caclark
5 - Atom

Thank You, I inherited this process from someone who is no longer with our company and I was unsure of his intent with the formula. I appreciate your assistance. 

Ben_H
11 - Bolide

@hanykowskaI totally agree.

 

While it's a quick fix to convert excel IF statements to IIF it doesn't necessarily aid with understanding.

 

The other problem being - much like Excel IF - IIF statements can rapidly become very difficult to read!

 

 

Labels