Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

Creating new columns based on repeat information by Unique Identified Number

Highlighted
8 - Asteroid

I have a process I am currenty using an Excel VBA macro to handle an I am hoping there is a way to do this programtically in Alteryx. 

 

The data has unique User ID records numbers. There is then a column for the "Business Rule" they are in. In most cases each user ID is in 1 or 2 of these and would be represente in the data by multiple rows.

 

Original data

User IDBusinessRule
123100
123101
234200
234201
234202
567300

 

I created a script that places each addtional role in a new tab with columns BusinessRule1, BusinessRule2, etc. 

 

The most # of rules that someone is in is 4. 

 

 

Result required

User IDBusinessRule1BusinessRule2BusinessRule3
123100101 
234200201202
567300  

 

Is there a way to do this? Can it be set up to automatically add addtional columns if there are 5 unique rules a user is in (or does that have to be hard coded? 

 

Appreciate the input. 

 

Regards,

 

Adam

Highlighted
ACE Emeritus
ACE Emeritus

Hi @aehrenwo

One solution is to Transpose, then use  MultiRow formula to help determine the new column names; then CrossTab; (see attached).

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

The Crosstab tool will do what you need if you create a header column using the Multi-Row Formula tool

 

Edit: Or what @JohnJPS said

Highlighted
6 - Meteoroid

Thanks for this. Was spending hours trying to do this in an iterative macro and this made it so easy!

Labels