convert excel formula into Alteryx formula

[AvinashBhawsar]

Hi All,

I hope everyone is doing well. can anyone help me out to convert the below formulas into alteryx compatible.

=IF(D2="","",IF(OR(W2="Reconciled",W2="Timing Delays"),0,IF(AND(Q2="Same",R2>0),R2+1,IF(Q2="New",1,"ERROR"))))

D2 represent the column :- SellerLoanNum

W2 represent the column :- NOTES

Q2 represent the column :- Compare Notes)

R2 represent the column :- Prior Age

 =IF(A2="PASTE DATA HERE","",IF(ISNA(IF(P2=U2,"Same","New")),"",IF(P2=U2,"Same","New")))

A2 represent the column :- SellerId

P2 represent the column :- Prior Day Notes

U2 represent the column :- Breaks Check

Thanks for taking out time to read 

[atcodedog05]

Hi @AvinashBhawsar 

Can you provide some sample input and expected output It will help us get a better understanding of the usecase.

We will be happy to help : )

[Luke_C]

Hi @AvinashBhawsar 

Sample data is always helpful, but using the IF THEN ELSE syntax, the first formula would look something like this. You may need to tweak slightly based on the data types of your fields.

IF isempty([SellerLoanNum])
then [SellerLoanNum]
Elseif [NOTES] in ('Reconciled','Timing Delays')
Then 0
Elseif [CompareNotes] = 'Same' AND [Prior Age] >0 
Then [Prior Age] + 1
Elseif [CompareNotes] = 'New'
Then 1
Else 'ERROR'
Endif

Give this a go and see if you can complete the second one using this as an example. A tip for you, the formula tool will help give you the syntax. There is also an IIF function that mirrors the excel syntax, but for complicated logic I prefer the regular IF function.

[AvinashBhawsar]

here is the sample file. basically I would want the current age to be changed. There are multiple notes in the W (notes column), when I select the notes then current age will change as per the formula in the sample file. I would request you to refer the formula in column Q (compare notes) and column S (Current Age).

note :- I have changed the data due to confidentiality. @Luke_C @atcodedog05 

[Luke_C]

@AvinashBhawsar Thanks, did my suggestion work though? Any errors in the workflow? 

[AvinashBhawsar]

Thanks for the suggestion Luke :)

I tried however, it is giving me an error and asking to use tonumber as the current age column has numbers. I selected current age as output in the formula tool.

[Luke_C]

@AvinashBhawsar Your logic will not work in the current age field, since it is numeric we cannot print 'Error'. Below is an updated formula I got to work (made compare notes field name correct and updated the last else clause to output null() instead of error. 

Since we did not have any values in the notes field I had to use a select tool to make it a string for this to work.

IF isempty([SellerLoanNum])
then [SellerLoanNum]
Elseif [NOTES] in ('Reconciled','Timing Delays')
Then 0
Elseif [Compare Notes] = 'Same' AND [Prior Age] >0 
Then [Prior Age] + 1
Elseif [Compare Notes] = 'New'
Then 1
Else null()
Endif

[AvinashBhawsar]

@Luke_C thanks for the help and suggestion. i tried this formula however, it is giving me an error and not allowing to end the expression.

my apologies for late reply as I was busy with some other work. :)

[Luke_C]

@AvinashBhawsar What data type is 'Notes'? Try ToString([Notes]) 

[AvinashBhawsar]

@Luke_C it is text