Alteryx Designer Discussions

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

How to transpose all columns to rows?

diabolic
7 - Meteor

Hi,

 

For example, I have data like below:

F1F2F3F4F5F6F7F8
AAA0001AAA0002AAA0003AAA0004AAA0005AAA0006AAA0007AAA0008
973,880.894,782,158.16121,393.21282,760.28298,333.7289,445.06106,041.15104,381.48

 

The number of columns is not fixed (could be more than 1K).

 

I want to change my data to below:

F1AAA0001973,880.89
F2AAA00024,782,158.16
F3AAA0003121,393.21
F4AAA0004282,760.28
F5AAA0005298,333.72
F6AAA000689,445.06
F7AAA0007106,041.15
F8AAA0008104,381.48

 

I have tried to use "Transpose", but I do not get what I want.

 

Any suggestion? Thanks.

4 REPLIES 4
BenMoss
ACE Emeritus
ACE Emeritus
A couple of ways to do this.

Use the transpose tool and have everything else as a data field..

Then use a summarize tool. Grouping by the name field and then concatenating the value field..

Finally use the text to columns on the concatenated field to split it out to different columns.

Ben
BenMoss
ACE Emeritus
ACE Emeritus

Just to add to this, having though about it for a bit longer, would be the following:

 

Record ID your data. When doing the transpose use the recordID as a key field and then transpose everything else.

 

Then use the cross-tab tool. Your name field will be your 'group by', your RecordID field will be the header field, and then the value field will be your 'value', as you will only have one record in each at the bottom of the cross-tab tool select 'concatenate'.

 

This will be dynamic to give you three columns if you had three rows. Whereas the first solution you would have to change the number of columns to split to.

 

Ben

Kaviyarasan_P
8 - Asteroid

Answer:Capture.JPG

razzy
8 - Asteroid

This is what i have i am trying to use your idea but it deos not seem working for me. I have this data set:

Col A     Col B     Col C


Light on AB WT 0
Light on AB WT 0
Light on AB WT 3072
Light on AB WT 704
Light on AB WT 626
Light on AB WT 634
Light on AB WT 1924
Light on AB WT 2472
Light on ndufs2 Control 0
Light on ndufs2 Control 0
Light on ndufs2 Control 0
Light on ndufs2 Control 0
Light on ndufs2 Control 0
Light on ndufs2 Control 0
Light on ndufs2 Control 0
Light on ndufs2 Control 0
Light on AB 272 25 µM 0
Light on AB 272 25 µM 0
Light on AB 272 25 µM 1376
Light on AB 272 25 µM 0
Light on AB 272 25 µM 814
Light on AB 272 25 µM 0
Light on AB 272 25 µM 4
Light on AB 272 25 µM 0
Light on ndufs2 272 25 µM 0
Light on ndufs2 272 25 µM 0
Light on ndufs2 272 25 µM 60
Light on ndufs2 272 25 µM 1594
Light on ndufs2 272 25 µM 146
Light on ndufs2 272 25 µM 2104
Light on ndufs2 272 25 µM 0
Light on ndufs2 272 25 µM 0
Light on AB 259 50 µM 0
Light on AB 259 50 µM 276
Light on AB 259 50 µM 126
Light on AB 259 50 µM 222
Light on AB 259 50 µM 0
Light on AB 259 50 µM 0
Light on AB 259 50 µM 70
Light on AB 259 50 µM 4522

 what i want to do is to transpose and get a matrix like this:

1.0.0.3072.704.626.634.1924.2472.
2.0.0.3406.2036.398.1222.2628.0.
3.20.8.2744.1926.306.126.2492.0.
4.0.10.2490.2798.0.2268.2442.40.

How do i solve this thanks

 

Labels