Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Passing Field Names with Non-alpha Characters into a Formula Field

daniel_mmi
9 - Comet

I'm building a macro that reads the fields present in an input file and uses them to populate a dropdown menu. The resulting selection is passed into a Formula tool. If the original field name contains no spaces or strange characters (-, /, maybe more), it works perfectly. If the field *does* contain those characters, as in "E-Mail" or "Zip Code", the result will be passed as "E" or "Zip" respectively. Because there *is* no variable/field named "E", that's as far as it gets and throws an 'unknown variable' error.

 

I'm sure there is a simple solution, but I'm stuck *so* close to having a working macro.

 

Thanks!

Daniel Taylor

6 REPLIES 6
chris_love
12 - Quasar

Hi Daniel,

 

Check what you are replacing in the Formula, don't replace the [Field], just replace Field. You'll need to leave the square brackets so that fields with spaces in get them properly.

 

I hope that makes sense.

 

e.g. 

 

IF [Sales] > 100 then 'High' else 'Low' endif

 

if you replace [Sales] with Sales Value it becomes

 

IF Sales Value > 100 then 'High' else 'Low' endif

 

which doesn't parse, so make sure you only replace the text Sales (not including the square brackets)

 

Chris

daniel_mmi
9 - Comet

Thank you, that worked perfectly!

 

This is sort of a separate question, but in this case, is there a way I can write the expression such that it won't fail when it gets a [None] selection from the Drop Down?

chris_love
12 - Quasar

it depends what you want to happen when there is a None section, it's certainly possible but more complicated. In this case I'd use an action to "Replace with Formula" and then craft individual formula to add into the tool depending on the selection. It's harder because you need to watch your quotes.

 

e.g. if the formula was as above but we wanted to set everything to Low when the Dropdown was "None" I'd use a Replace with Formula action with the following formula:

 

IF [Dropdown] = 'None' then '"None"' else 'IF [' +[Dropdown]+'] > 100 then "High" else "Low" endif' endif

 

Note how I'm mixing up Single and Double quotes to ensure they parse correctly when they get passed into the Formula. 

 

Good luck!

daniel_mmi
9 - Comet

'Update Value with Formula' -> Perfect. Much simpler code, too.

 

if isempty([#1]) then '""' else '['+[#1]+']' endif

 

Thanks again. I'm just finishing building a macro that will add a set of our standard fields at the beginning of an input file, with dropdowns for field mapping, populated by the fields in the macro input. Not too shabby for my first week using Alteryx. Quite a product you guys have, here.

chris_love
12 - Quasar

Holy **bleep** - you've been using it a week! Kudos to you my friend, thats some learning curve right up to macros already. Great work.

 

Totally agree with you on how great a tool it is, I've been a customer for years and it still surprises me what you can do.

Itay_K
7 - Meteor

Hi,

 

Could you please upload your solution?

 

Thanks!

Labels