Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.
SOLVED

Unable to fix IF statement error

Highlighted
8 - Asteroid

Hi all,

I am trying to write a nested IF statement.

in the input file, there is a column currency. that has value as AED, BHD, SAR etc.

another column has the actual stock value.

My goal is to convert all other currency to AED.

for this, I want to multiply other currency with conversion factor and multiply AED with 1 so it remains same.

Written then below IF statement, but getting error.

how is it wrong?

 

IF [Crcy] = "AED" then [Stock Value] = [Stock Value]*1
Elseif
[Crcy] = "BHD" then [Stock Value] = [Stock Value]*9.84
Elseif
[Crcy] = "EGP" then [Stock Value] = [Stock Value]*0.22
Elseif
[Crcy] = "QAR" then [Stock Value] = [Stock Value]*1.01
Else
[Crcy] = "SAR" then [Stock Value] = [Stock Value]*0.98
ENDIF

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi @saikathalder ,

 

change your formula to:

IF [Crcy] = "AED" then

[Stock Value]*1
Elseif
[Crcy] = "BHD" then

[Stock Value]*9.84
Elseif
[Crcy] = "EGP" then

[Stock Value]*0.22
Elseif
[Crcy] = "QAR" then

[Stock Value]*1.01
Else
[Stock Value]*0.98
ENDIF

 

The "ELSE" requires no condition. Hope this is helpful.

 

Best,

 

Roland

 

Highlighted
10 - Fireball

Try this....

 

IF [Crcy] = "AED" then [Stock Value]*1
Elseif
[Crcy] = "BHD" then [Stock Value]*9.84
Elseif
[Crcy] = "EGP" then [Stock Value]*0.22
Elseif
[Crcy] = "QAR" then [Stock Value]*1.01
Else
[Crcy] = "SAR" then [Stock Value]*0.98
ENDIF

 

8 - Asteroid

I want to create a new column as well. How will alteryx identfy which column to write the multiplied data?

Highlighted
14 - Magnetar

In Alteryx if statements, you don't say if then [Stock Value] = [Stock Value]*1, the statement should look like this:

 

IF [Crcy] = "AED" then [Stock Value]*1
Elseif
[Crcy] = "BHD" then [Stock Value]*9.84
Elseif
[Crcy] = "EGP" then [Stock Value]*0.22
Elseif
[Crcy] = "QAR" then [Stock Value]*1.01
Else
[Crcy] = "SAR" then [Stock Value]*0.98
ENDIF

 

This is because you're already telling the Formula tool which field you're updating.

Highlighted
14 - Magnetar

In the formula tool, under Select Column, pick Add new column and give your new column a name.

Highlighted
14 - Magnetar

DavidP_0-1582552654317.png

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hello @saikathalder ,

 

When writing IF conditions you don't select the field to write on the text, you do it on the column name (red circle). You only need to define the conditions.

 

Also, instead of writing a long if that can have many more currencies I changed the logic to apply using a join tool:

 

Untitled.png

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

Highlighted
Alteryx Partner

EDIT: Ignore this, just realised is the same approach posted above.

 

hI @saikathalder ,

 

May I suggest you an alternative approach? I created a currency list using the text input and then joined this with my other source to create the formula. The benefit of this is that if the currency rate changes then you can update it easily without having to go to each formula.

 

DiegoParker_0-1582560553682.png

 

Find the workflow attached.

 

Hope this helps.

 

Best,

Diego

 

Highlighted
8 - Asteroid

Thanks all for the inputs, for now, I will just go for a simple IF statement and calculate it in the formula box. thanks.

Labels