Hi,
I am trying to cleanse some messy survey data. The survey has a number of questions that are multiple choice. The respondent is able to select a number of choices per answer. Consequently, when viewed in Alteryx the cell could look like this;
| Respondent_010 | answer a, answer b, answer d |
| Respondent_011 | answer a |
| Respondent_012 | answer a, answer d |
Cleansing this is relatively easy, Ive been using text to columns on commas, transposing and then filtering out the nulls.
However, one of the multiple choice options is "Other" this then allows the respondent to write free text;
| Respondent_001 | answer a, answer d |
| Respondent_002 | I am not impressed with this questionnaire. I think it is a waste of my time, you have wasted resource. |
I want to treat these answers differently. Im unable to filter on "Other" as that isn't contained in the data, only their free text response.
I want to filter all the "normal" answers e.g. "answer A, answer B" and then treat the free text differently so ultimately want to split them out into two streams of data. The difficulty is the combination of answers available, so I cant simply write an if statement with if field = "Answer A, Answer B" then......
Having looked through the community I tried a if REGEX_Match formula;
if REGEX_Match ([Field_A], "answer A|answer B|answer C| answer D" then "Text to Columns" Else "Free Text" endif
However this didnt work and I now don't know how to proceed.
Any ideas?
Thanks in advance.
Alex
