Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

External mapping file to match string texts

Hi all,


I have a table of customer feedback (verbatim) that I would like to to 're-map' to the proper Themes and Sub-Themes based on the keywords in the verbatim.


I would like to maintain an external mapping file that can be updated at any time with the updated mapping. As an example, my external file looks like this, using food data ;)


CategoryThemeTheme KeywordSub-ThemeSubTheme Keyword
FruitsCitrus'orange' OR  'lemon' Colour('orange' or 'yellow') and 'colour*'
FruitsCitrus'orange' OR  'lemon' Smell('nice' and 'citrusy') or 'awful'
FruitsCitrus'orange' OR  'lemon' Taste('sour' and 'juicy') or 'tasty'
FruitsStonenectarine' OR 'peach' OR 'apricot'Smellsummer' or 'sour'
FruitsBerriesblueberry' AND 'strawberry' AND 'raspberry'Tastedelicious' or 'sour' or soft'
FruitsBerriesblueberry' AND 'strawberry' AND 'raspberry'Smellneutral' and 'fun*'


The keywords have logical OR/AND conditions, as well as wild card '*' for words like 'work*' to map to 'working, worked, works' etc.


I would like to map a table like above to my customer feedback table based on the Category column. 


As an example, if the verbatim is in Category = Fruit, and contains the words ('orange' OR 'lemon'), then I'd like to update the Themes column to Citrus.

If the theme is Citrus, and verbatim also contains the words ('orange' OR yellow) AND 'colour*', then the Sub-Theme would be 'Colour'.


Can someone please advice what the best way to achieve this? And where would be the best place to put the logic for the themes and sub-themes such that there would be minimal changes required for future updates? (if not in this external file).


Would really appreciate your help and feedback!


Thank you in advance.


Kind Regards,



@viv_ienne This task is easier said than done :) I would have a look at the dynamic replace tool. The tool is powerful and quirky, so I've setup the attached to see if it can point you in the right direction. Save the workflow and excel file to the same directory on your machine and it should run. It's not very easy on the eyes when you're trying to build a formula dynamically. 


I'm not sure of an easy way to handle complicated and/or statements. In my example, I'm essentially treating everything as an or statement.