Hi Community,
I am still new to Alteryx and many of its features. My current requirement is such that I have a key of Response ID and Question ID and another dataset wherein I have column headers with the Question ID attached with some Sub-Question ID ex. 108_#Q65_1_1. They are arranged according to the Response ID. A snippet of that is as follows:
Key:
ResponseId | ID2 |
R_3oNklJ0xzVqTu6y | 198 |
Dataset:
ResponseId | 108_Q29#1_1_1 |
R_10pyjpbpaiKuIAe | Close the 2 Austrian Business Units and have Bonn and Berlin takeover their customers |
The dataset contains many such columns with multiple Question ID. Is there a way that I can get the data in the following way wherein I get the Columns according to the SubQuestion ID and the rows according to the Question ID and Response ID?
ResponseId | ID2 | Q29_1_1_1 |
R_10pyjpbpaiKuIAe | 108 | Close the 2 Austrian Business Units and have Bonn and Berlin takeover their customers |
R_2wBOCXdJ5lPklDk | 198 | Integrate the relocating Austrian employees with the existing Bonn business unit employees |
Also is it possible to make it a loop as the size of the key is not fixed?
Any help is appreciated and I have attached a mock data sheet with the dataset and key as well.
Thanks.
Solved! Go to Solution.
Hello @AdityaJoshi1 : attached is an attempt at parsing this out for you. Let me know if you have questions.
Hi @morr-co,
Thank you for the reply and the solution. The thing is that in the given solution the if there are more than one responses under a Response ID they are concatenated to each other in the columns but they are supposed to be on a different row with the same Response ID.
Like
R_10pyjpbpaiKuIAe |
R_10pyjpbpaiKuIAe |
and each row will have a set of responses.
Hi @AdityaJoshi1 breaking each response to its own row should be feasible by adding that criteria to the Group in the crosstab tool. You would just need to know which value distinguishes the responses. After transposing the records, I see this, for example:
108_Q29#1_1_1 |
108_Q29#1_2_1 |
108_Q29#1_3_1 |
108_Q29#1_4_1 |
... |
Is it the second number (in bold) above distinguishing the response number? If not, what is? You would just need to capture that in the text to columns tool and - as mentioned above - include it in the Group in the crosstab tool.
Hi @morr-co,
Yes, I was actually trying different things out and I stumbled on your described solution.
Thank you so much for your help.