Alteryx Designer Discussions

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

HOW TO: Add a continous number as prefix for columns

Czaggy
7 - Meteor

Hello,

 

is there a way to add a continous count or number as a prefix to the column titles?

I did not find a solution with the dynamic rename tool. 

 

I hope someone can help me out, below is an example 🙂

 

Input:

CategoryBananaAppleKiwiCucumberOrange
Weightlightlightlightlightlight
Coloryellowredbrowngreenorange

 

Output:

01Category02Banana03Apple04Kiwi05Cucumber06Orange
Weightlightlightlightlightlight
Coloryellowredbrowngreenorange
3 REPLIES 3
Alekh
8 - Asteroid

You can transpose the column names and add a record ID and combine them. I made a condition that if the Record ID was between 1-9, it'll add a 0 in front of it. 

Then cross-tab them and rename. I've used a dynamic rename as the cross-tab will convert special characters and spaces to underscores. Using the record ID as the header name in the cross-tab and changing it in the dynamic rename will means that you'll always have the original header name with the number as the prefix.

EDIT: Didn't realise there was more than 1 row. I just added the select records (could've used Sample tool grabbing the first row and the other skipping the first row) so that the first row can be used to transpose/cross-tab and then rest of the data is unioned at the end.

2019-07-24 19_33_34-Alteryx Designer x64 - Column Rename.yxmd_.png

 

 

 

 

 

 

 

 

 

 

You can also use the field info tool so that you don't have to split the data or use the transpose.

aldesigner.png

 

 

 

 

 

 

 

 

 

 

I've attached a workflow of both

klyuka
8 - Asteroid

column_rename.png

I am also new to Alteryx. I suggest to do this by formula in Transpose - CrossTab cycle with a table,

Once you have time for study - I sugest Weekly Challenges (or just look the solutions)/

Alex

danilang
18 - Pollux
18 - Pollux

Hi @Czaggy 

 

Both of the previous solutions are good but using Transpose or Crosstab tools can be very expensive if you have lots of data in your table.  Here's a solutions that avoids those, using a minimal number of tools

 

WF.png

Start with the Field Info tool to extract the list of column names in the order that they appear.  Add a record ID which is then prepended to the field name.  The Dynamic Rename tool takes the fields from the original input and renames them using the new names from R input.

 

Result.png

 

Dan

Labels