Creating new column using Formula Tool to create a percentage output

[jjimenez]

Hello!

I need help with this last portion of my workflow. I'm creating a new column "MAX CEILING" and based on the Name in another column, the new column will populate a different output.

I want to populate "1.30" if Name in another column is "Foreground"

If Name is "Background" in same column then populate "1.50" on each row.

2nd formula:

If Name is "KVI" then pull data from column "Target Index" and "Super KVI" pull data from "Target Index" on populate what's present in that row.

Here is a snapshot: I'm getting Parse Error at char(35): Malformed if Statement (Expression #1)

Any help will be greatly appreciated. 

[PhilipMannering]

The first thing to note is you're missing an ELSEIF statement where the red squiggly line is. Can you share the workflow with an excerpt of the data?

[jjimenez]

Thank you Philip,

How do I share just an excerpt without sending sensitive data? Sorry, I'm not the best with Alteryx

[Kenda]

Hi @jjimenez 

@PhilipMannering is right on about the missing "ELSEIF" pieces of your expression.

One other option could be to use the switch function. I built an expression that looks like this: 

Switch([ROLE],[TARGET_INDEX],"Foreground", "1.30","Background", "1.50")

This is saying to look in the [ROLE] field. By default, the value will be [TARGET_INDEX]. But if the ROLE is Foreground, the value will switch to 1.30. If the ROLE is Background, the value will switch to 1.50. This will work so long as this logic matches up with your expectations (i.e. the Foreground and Background are the only manual values you want to se and all else can be set to TARGET_INDEX).

Note the new field you're creating is a string. If, instead, you want this to be a numeric field, switch the data type in the formula tool and take away the quotation marks surrounding the numbers in the expression.

[jjimenez]

I tried the "ELSEIF" expression and removed it. I will try again.

I will keep you posted. 

[PhilipMannering]

You could copy and paste all the tools into a new workflow, replace the Input Tool (with sensitive data) with a Text Input and copy in a few rows of data. Change the values slightly if necessary.

Does that make sense?

[jjimenez]

Hey Philip - sorry it took so long. It's a long workflow and had to alter many inputs to avoid any sensitive information. 

[PhilipMannering]

Hey @jjimenez this isn't going to work because I don't have your connections. Try recreating the data with a text input just before the last Formula Tool (as this is where you're seeking help). I would expect your workflow to look more like this,

Let me know if this makes sense.

[jjimenez]

Here you go.

[PhilipMannering]

I think you can use what Kenda shared, but only wrap the function in a to_number() function,

tonumber(
Switch([ROLE],[TARGET_INDEX],"Foreground",1.30,"Background",1.50)
)

Although be warned that if ROLE is not Foreground or Background then it will take the value in [TARGET_INDEX] - and if this is Null then I believe tonumber(Null()) gives you 0, which may not be what you want. Let me know if the attached works.