Alteryx Designer Discussions

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

Crosstab duplicate rows into as many columns as we need

arthurmauk
6 - Meteoroid

Hi, I have a dataset that looks like this:

 

IDNameState
1JohnApproved
1SteveApproved

 

I need to get it to something like this:

 

IDName1State1Name2State2
1JohnApprovedSteveApproved

 

I was attempting this using Crosstab but I couldn't make it work, is it a different tool I need or am I using it wrongly? Thanks in advance.

3 REPLIES 3
atcodedog05
21 - Polaris

Hi @arthurmauk 

 

This is how you can do it. You would need a group ID to get columns separate 

 

Workflow:

atcodedog05_0-1622627628255.png

 

1. Using transpose convert columns to rows.

2. Using multi-row formula with groupby on ID, Name (column name) create group ID.

3. Using formula tool conact to column name

4. Using crosstab tool ID as ID, New column names as column name & value as value convert it to table.

 

Hope this helps 🙂

arthurmauk
6 - Meteoroid

Perfect, this is exactly what I wanted, thanks!

 

I now have a new problem where the Crosstab sorted all my columns into Name1, Name2, Name3, ... Name 36, State1, State 2... I would like to change the sort to Name1, State1, Name2, State2, etc. How would I go about this? Thanks in advance again!

atcodedog05
21 - Polaris

Hi @arthurmauk 

 

Sadly there is no easy way of doing it. Here is how you can do it.

 

Workflow:

atcodedog05_1-1622629224122.png

 

When columns are crosstabed it is arranged in ascending order of column names. Hence i am creating an column using record ID which will be the column name by that the actual columns would be sorted in the required manner.

 

1. Sorting based on GroupID and Name. So that we get Name1, State1, Name 2, State 2...

2. I am using recordID and setting position. Since ID(pre-existing column) is not there in the name rows i am adding it using union tool.

3. Crosstabing it with name as recordID and value as new name. This way column name is 1,2,3.. (position holder) under that actual column names as 1st row data.

4. Using dynamic rename tool i am changing column name by taking names from 1st row. Now we have columns with position maintained.

5. While union tool the output data always maintains column order of the first connection. Hence column names order is first connection and data is second. This way you will get columns in desired order. 

 

Hope this helps 🙂

 

Labels