Alteryx Designer Discussions

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

help with if and adding prefix formula

dxia0919
5 - Atom

I'm trying to create a formula where if each column is more than 0%, then add a prefix for either "LIBOR+" or "PRIME+" in front of the column in the new column. the below formula keeps giving me the error malformed if statement - can someone please help? also attached an example data below: 

 

IF [Loan Libor Spread2] >0 THEN "LIBOR+" + [Loan Libor Spread2] else if [Loan Prime Spread2] >0 then "PRIME+" + [Loan Prime Spread2] elseif [Loan Fixed Spread2] >0 then [Loan Fixed Spread2] else "" endif

 

starting: 

Loan Libor Spread2Loan Prime Spread2Loan Fixed Spread2
0.1%  
 1% 
  2%

 

   

Goal: 

Interest Rate
LIBOR + 0.1%
PRIME+1%
2%

                    

3 REPLIES 3
Luke_C
17 - Castor

Hi @dxia0919 

 

You're getting the malformed if statement error because you're trying to perform math functions on strings. A simple workaround would be to wrap a tonumber() around each field. You also had an 'else if' instead of 'elseif'

 

IF ToNumber([Loan Libor Spread2])>0 THEN "LIBOR+" + [Loan Libor Spread2]

Elseif ToNumber([Loan Prime Spread2]) >0 then "PRIME+" + [Loan Prime Spread2]

elseif ToNumber([Loan Fixed Spread2]) >0 then [Loan Fixed Spread2]

else "" endif

ncrlelia
11 - Bolide

HI @dxia0919,

 

Agree with Luke, also I noticed that is an extra space in the ELSEIF in your IF statement

ncrlelia_0-1660092528685.png

 

Cheers,

Lelia

grazitti_sapna
17 - Castor

@dxia0919, another way is to compare it with a string value.

IF [Loan Libor Spread2] >'0' THEN "LIBOR+" + [Loan Libor Spread2] elseif [Loan Prime Spread2] >'0' then "PRIME+" + [Loan Prime Spread2] elseif [Loan Fixed Spread2] >'0' then [Loan Fixed Spread2] else "" endif

grazitti_sapna_0-1660104728472.png

 

I hope this helps!

 

Thanks!

Sapna Gupta
Labels