ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
1 Day Left! - The Alteryx Community will be temporarily unavailable for a few hours due to implementation of the new SSO experience starting tomorrow at 5pm MDT. Please plan accordingly. For more information, read the blog.

Alteryx Designer Discussions

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

Formula tool not working for my If formula for a string value

JonaV
7 - Meteor

Hi,

 

I would appreciate some help on this issue

 

I have a formula as follows

 

Event Year (field nam)

IF [EP_Scenario]="2019Actuals" THEN "2019" ELSE IF [EP_Scenario]="2020Actuals" THEN "2020" ELSE "" ENDIF ENDIF

 

However, when I run the workflow and check the data in the Event year field there are blanks instead of the 2019 and 2020 years that I specified in the formula. Does anybody why this is happenning?

 

Thank you,

 

JV

atcodedog05
17 - Castor

Hi @JonaV 

 

Check the datatype set as the formula output datatype. 

 

"2020" is string

2020 is number

 

You might have set something numeric

 

You can use this instead of nested IF

 

IF [EP_Scenario]="2019Actuals" THEN "2019" ELSEIF [EP_Scenario]="2020Actuals" THEN "2020" ELSE "" ENDIF

 

 

Here is a workflow for the task.

Output:

atcodedog05_0-1604508572472.png

Workflow:

atcodedog05_2-1604508611400.png

 

Hope this helps 🙂


If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

 

Jean-Balteryx
14 - Magnetar

Hi @JonaV ,

 

Can you share a sample workflow ?

 

Cheers,

 

Jean-Baptiste

Jonathan-Sherman
15 - Aurora
15 - Aurora

Hi @JonaV,

 

Could you try

 

IF [EP_Scenario]="2019Actuals" THEN "2019" ELSEIF [EP_Scenario]="2020Actuals" THEN "2020" ELSE "" ENDIF

 

You're ELSE IF should be ELSEIF and you only need one ELSEIF on the end of the formula.

 

If this solves your issue then please mark the answer as correct, if not let me know!

 

Regards,

Jonathan 

Jonathan-Sherman
15 - Aurora
15 - Aurora

Or taking your nested IF function approach you'd need to use

 

IF [EP_Scenario]="2019Actuals" THEN "2019" ELSE IF [EP_Scenario]="2020Actuals" THEN "2020" ELSE "" ENDIF ELSE "" ENDIF

 

But i'd still take the first approach as I think it's simpler!

 

If this solves your issue please mark the answer as correct, if not let me know!

 

Regards,

Jonathan 

JonaV
7 - Meteor

@atcodedog05 

The data type is right. Year is a string value not a number. I am not trying to use the Event Year field as a measure.

 

For some reason it is doing the same in other formulas as well

 

I am not sure if I should use something other than quotes ("") to specify a string within the EP_Scenario field (which is also a v_string data type)

JonaV
7 - Meteor

Here is a snapshot

 

JonaV_0-1604508367203.png

 

atcodedog05
17 - Castor

Hi @JonaV 

 

I have attached a sample workflow in the above post can you please check

atcodedog05
17 - Castor

Hi @JonaV 

 

Can you provide sample data about 5-10 rows will do.

Jean-Balteryx
14 - Magnetar

@JonaV , is there any spaces in values of EP_Scenario ?

 

Cheers,

 

Jean-Baptiste

Labels