Need to re write an Excel Formula into Alteryx

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

Expression

Accepted answers

clmc9601

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

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. Alteryx Formula.PNG

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

All comments

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

Hi @Aparanjith,

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

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(), '-')"

I hope this helps!

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167