Hello,
A very basic programming feature that Alteryx doesn't seem to have is the ability to change multiple columns to varying outputs if a condition is true.
For example:
If contains([text], "German Shepard") AND (contains([text], "dog") or contains([text], "Canine")) then
[genus]="Canis" and [species]="German Shepard"
else [genus] = null() and [species]=null()
endif
For my use case, I'm looking at text, and if it contains certain keywords, that informs four other columns (i.e., text is placed into four other columns). Right now, every time I want to add one item, I have four separate formulas I have to update. I have hundreds of these keywords so you can imagine how annoying and impractical this all is to keep all of these formulas synced. What can I do to easily overcome this? Is there a tool I'm missing?
edit: I know the multi-field formula will apply the same equation to multiple columns, but I need different values in those columns if a match is detected.
.
@theinsideguy would a lookup table work for you? The 'Find Value' works in the exact same way as a Contains() function and you then have the option to append fields if a match is found. By means of a quick example, here we have some random text about animals:
And here we have our lookup table. Using the Find and Replace tool, we have the option to look through our target (find within) field - in this case [Text], for matches against our find value, [Target] field. As you can see in the configuration, we've set this to be case insensitive and also to be able to find the match anywhere within the field, rather than solely the start/end. Upon a successful match, we've also chosen to append the necessary fields from the lookup - [Genus], [Species] & [Animal]:
The results:
This may be a bit of a pain to set up but the tool configuration is a one off and then moving forward it's just a case of adding a new row with the relevant lookup word/phrase and corresponding values for the other fields each time you need to expand this.
Thank you for the potential solution, but my formulas are a little more complicated, often containing a lot of logic operations and multiple words. I believe the find and replace can't do that?