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))
Solved! Go to Solution.
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
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
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 🙂
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.
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
Thank You, this worked!
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.
@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!