Need to re write an Excel Formula into Alteryx

[Aparanjith]

Hi All,

I have recently joined into one of the Alteryx project where we are trying to replicate the Excel formula into Alteryx. I am currently stuck with one of the formula which is hard for me to replicate in Alteryx. Can anyone help me out in this?

Below is the SQL formula for a column;

if(if( isnull([OPV]) or [OPV] = 0, CVA * -1 + RecoveryA* -1, [OPV] * -1) <> 0
AND (match(DenialType,'Information Only','Information Only NO REMIT')=0 or isnull(DenialType)=-1 or DenialType='-')
AND (LastActivityStatus <> 'Single Case Agreement' or isnull(LastActivityStatus)=-1 or LastActivityStatus='-')
,if( isnull([OPV]) or [OPV] = 0, CVA * -1 + RecoveryA* -1, [OPV] * -1),0) AS [Initial D],

[Aparanjith]

I have break them into different formulas and made a final column out of it. Can someone let me know how can we combine to one formula rather than using multiple formulas?

-- FormulaA - IF ISNULL(OPV) OR [OPV] = 0 THEN OPV * -1 + RecoveryA * -1 ELSE [OPV] * -1 END IF

-- FormulaB - IF DenialType NOT IN ('Information Only','Information Only NO REMIT') THEN 0 ELSE 1 END IF

-- FormulaC - IF ISNULL(OPV) OR [OPV] = 0 THEN CVA * -1 + RecoveryA * -1 ELSE [OPV] * -1 END IF

-- Final Formula - IF FormulaA <> 0 AND (FormulaB = 0 OR isnull(DenialType)=-1 OR DenialType='-')
AND (last_activity_status <> 'Single Case Agreement' or isnull(last_activity_status)=-1 or last_activity_status='-')
THEN FormulaC
Else 0

[clmc9601 · Accepted solution]

Hi @Aparanjith,

Here are some ways you can simplify your formula. If you can use multiple formulas (especially all within the same tool), sometimes that makes it easier to understand and debug. Combining them could be hard to troubleshoot later on.

-- FormulaA = IF ISNULL(OPV) OR [OPV] = 0 THEN OPV * -1 + RecoveryA * -1 ELSE [OPV] * -1 ENDIF

-- FormulaB = IF DenialType NOT IN ('Information Only','Information Only NO REMIT') THEN 0 ELSE 1 ENDIF

-- FormulaC = IF ISNULL(OPV) OR [OPV] = 0 THEN CVA * -1 + RecoveryA * -1 ELSE [OPV] * -1 ENDIF

-- Final Formula = IF FormulaA <> 0 AND (FormulaB = 0 OR isnull(DenialType)=-1 OR DenialType='-')
AND (last_activity_status <> 'Single Case Agreement' or isnull(last_activity_status)=-1 or last_activity_status='-')
THEN FormulaC
Else 0

Green - can use "[OPV] IN (Null(), 0)"

Blue - just do "- [OPV] - [RecoveryA]"

Formula C appears to be identical to Formula A

Pink - just do "[DenialType] IN (Null(), '-')"

Purple - same concept as pink: "[last_activity_status] IN (Null(), '-')"

You can combine them all into a single formula by replacing any instance of "Formula B" with whatever it represents. For example, replace orange Formula B from within Final Formula with the equation after it: "IF DenialType NOT IN ('Information Only','Information Only NO REMIT') THEN 0 ELSE 1 ENDIF" Again, this leaves you with a complicated, messy-looking formula. But it should accomplish your goal.

I hope this helps!

[Aparanjith]

Combining the complete formula into single one is not working in Altreyx, it is giving syntax error in the formula tool.

see the attached sample If(IF example in Alteryx.

If(If [ADDR] = 'ABR' THEN 0 ELSE 1 endif)
!= 0 Then 'correct' ELSE 'wrong' ENDIF

[clmc9601]

With this specific example, I added a space between the two IFs and it worked just fine:

If (If [ADDR] = 'ABR' THEN 0 ELSE 1 endif)
!= 0 Then 'correct' ELSE 'wrong' ENDIF

[Aparanjith · Accepted solution]

I combined the whole formula into one as shown below and it worked fine now. Thank you! Attached is the screenshot of the formula in Alteryx.

IF (IF [OPV] IN (Null(), 0) THEN OPV * -1 + RecoveryA * -1 ELSE [OPV] * -1 ENDIF) != 0 AND ((IF [Denial Type] NOT IN ('Information Only','Information Only NO REMIT') THEN 0 ELSE 1 ENDIF) = 0
OR [Denial Type] IN (Null(), '-'))
AND (last_activity_status NOT IN ('Single Case Agreement') or [last_activity_status] IN (Null(), '-'))
THEN (IF [OPV] IN (Null(), 0) THEN CVA * -1 + [RecoveryA] * -1 ELSE [OPV] * -1 ENDIF)
Else 0
ENDIF