community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Transpose table to get column indices

Meteoroid

Hi there,

 

I have a table with column names as follows (with some duplicate column names):

 

[ID] [Date]          [Time]  [ColumnA] [ColumnlB] [ColumnC] [ColumnA] [ColumnB]

-------------------------------------------------------------------------------------------------------------

1     2017-11-20  17:20   50                20                 12              5                  40

 

When I transpose the table, Alteryx automatically renames my duplicates for me (which I dont want) e.g. Alteryx turns my 2nd ColumnA into 'ColumnA2'.

How do I transpose such that in my final output I have a column indicating column indices?

 

E.g. Output looks like:

 

 

 

[ID] [Date]       [Time]  [Column number] [Name]      [Value]

1   2017-11-20 17:20    4                            ColumnA    50

1   2017-11-20 17:20    5                            ColumnB    20

1   2017-11-20 17:20    6                            ColumnC    12

1   2017-11-20 17:20    7                            ColumnA    5

1   2017-11-20 17:20    8                            ColumnB    40

 

How do I:

1) Create the above column [Column number] (in red) which gives the original column indices before transposing? (i.e. value = 4 because it was the 4th column).

2) Stop Alteryx from re-naming my column names. Currently when I transpose the table, my 2nd ColumnA becomes ColumnA2.

 

Would really appreciate any pointers and advice.

 

Thank you!!

Viv

 

Alteryx Certified Partner

@viv_acious,

 

Depending in the file format type you’re reading in, theres an option in the Input Tools configuration that can tell Alteryx to use the first row as headers or read it as data (not worded like this exactly).

 

Transpose the columns you need and then drop a Dynamic Rename tool telling it to take the field names from the first row of data.

Meteor

Hai viv.

 

You can use fomula tool to change the name of column and use tile number to set the unique number each of column.
I attached the workbook for you.

 

Hope this will help you.

Alteryx Partner

Hi @viv_acious,

Here I attach a file that help solving your problem. First, as you said, you need to transpose your data. Next, you need to create two formula, which is:-

1) The first one is to create a new column for "Column Number", where you assign a number to your column (as before you transpose it)

2) The second formula to update "Name" column, where every name that has a number, you replace it

Then, the last one you just use Select tool to rearrange your column position.


Best Regard,
Syarif

Labels