Split column text on delimiter and choose index based on column

BobSikva

I have two columns from a likert question on a survey response:

Answer_Key: Semi-colon delimited list of possible answers

Answer: Numeric index for the selected answer (starts at 0)

Example (desired results):

Answer_Key: Strongly Agree;Agree;No Opinion;Disagree;Strongly Disagree

Answer: 0

Calculated_Text_Answer: Strongly Agree

Answer_Key: Yes;No;Maybe;N/A

Answer: 3

Calculated_Text_Answer: N/A

In a programming language, I'd split the Answer_Key into an array and index into the array using the Answer to get my Calculated_Text_Answer. How could I accomplish this in Alteryx?

MarqueeCrew

@BobSikva,

I was going to parse the data, but instead decided to make an assumption that your Likert scale has a maximum of 8 (scale of 7) values.  

1. Determine Scale by counting ";" semi-colons
2. Construct Regex expression (for use in a Replace function)
3. Construct Regex replacement by use of Answer
4. Execute Regex_Replace() function using #2 and #3 calculations

It worked!

Scale:

REGEX_CountMatches([Answer Key], ";")

Regex1:

IF 
[Scale] = 1 THEN "(.*?);(.*)" ELSEIF
[Scale] = 2 THEN "(.*?);(.*?);(.*)" ELSEIF
[Scale] = 3 THEN "(.*?);(.*?);(.*?);(.*)" ELSEIF
[Scale] = 4 THEN "(.*?);(.*?);(.*?);(.*?);(.*)" ELSEIF
[Scale] = 5 THEN "(.*?);(.*?);(.*?);(.*?);(.*?);(.*)" ELSEIF
[Scale] = 6 THEN "(.*?);(.*?);(.*?);(.*?);(.*?);(.*?);(.*)" ELSEIF
[Scale] = 7 THEN "(.*?);(.*?);(.*?);(.*?);(.*?);(.*?);(.*?);(.*)" 
Else ""
ENDIF

Regex2:

"$" + ToString([Answer]+1)

Calculated Answer:

REGEX_Replace([Answer Key], [Regex1], [Regex2])

Cheers,

Mark

Likert Answer.yxmd