Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Iterative Search and Join

AdityaJoshi1
6 - Meteoroid

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:

ResponseIdID2
R_3oNklJ0xzVqTu6y198


Dataset:

ResponseId108_Q29#1_1_1
R_10pyjpbpaiKuIAeClose 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?

 

ResponseIdID2Q29_1_1_1
R_10pyjpbpaiKuIAe108Close the 2 Austrian Business Units and have Bonn and Berlin takeover their customers
R_2wBOCXdJ5lPklDk198Integrate 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.

4 REPLIES 4
morr-co
10 - Fireball

Hello @AdityaJoshi1 : attached is an attempt at parsing this out for you. Let me know if you have questions.

AdityaJoshi1
6 - Meteoroid

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.

morr-co
10 - Fireball

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.

AdityaJoshi1
6 - Meteoroid

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.

Labels