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 ID | BusinessRule |
123 | 100 |
123 | 101 |
234 | 200 |
234 | 201 |
234 | 202 |
567 | 300 |
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 ID | BusinessRule1 | BusinessRule2 | BusinessRule3 |
123 | 100 | 101 | |
234 | 200 | 201 | 202 |
567 | 300 |
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
Solved! Go to Solution.
Hi @aehrenwo
One solution is to Transpose, then use MultiRow formula to help determine the new column names; then CrossTab; (see attached).
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
Thanks for this. Was spending hours trying to do this in an iterative macro and this made it so easy!