Alteryx Designer Desktop Discussions

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

Dynamic Rename using Column Values as Prefix + Crosstab

kwilda
6 - Meteoroid

I have seen examples of using a Column value to update a single new column name, but not multiples.

Looking to pivot each instance of PLANTYPE to a prefix for the 3 associated columns
So the resulting Columns would be DENTAL_CANCELREASON, DENTAL_CANCELDATE, DENTAL_CANCELYEAR
This would repeat for each PLANTYPE
With 40+ PLANTYPE values, this would result in 120+ columns, most world be null() 
resulting data needs to be grouped by the ID field, pivoting to 1 row per ID. Any help is appreciated


ID PLANTYPE  CANCELREASON           CANCELDATE CANCELYEAR
1   DENTAL       Family Needs Changed  12/31/2020        2020
1   TERM_C2    Family Needs Changed  1/1/2024            2024

 

ID DENTAL_CANCELREASON   DENTAL_CANCELDATE DENTAL_CANCELYEAR TERM_C2_CANCELREASON 
1   Family Needs Changed           12/31/2020                       2020                                 Family Needs Changed

 

TERM_C2_CANCELDATE TERM_C2_CANCELYEAR

1/1/2024                               2024

3 REPLIES 3
martinson
11 - Bolide

Dear kwilda I hope you're doing well.

 

I have a solution using the transpose and cross tab tools. This will work for larger data sets and should be dynamic.

 

Let me know if you have any other questions or issues. 

 

martinson

Bulien

 

 

 

Screenshot 2025-01-10 165806.png

Set up of the record ID
Screenshot 2025-01-10 174505.png

Set up of the transpose tool

Screenshot 2025-01-10 174242.png

Set up of the Cross Tab tool.

Screenshot 2025-01-10 174352.png

Cheers,
martinson

LinkedIN

Bulien
Carolyn
12 - Quasar
12 - Quasar

Hey! Give this a try!

 

  1. Ignore the bits in the container - I was a bit lazy with my copy/paste and then had to parse the data into columns. Your data wouldn't be like that so you wouldn't have to do it :)
  2. Transpose, with Key Columns = ID & Plan Type
  3. Formula Tool to Concatenate the PlanType & Name columns
  4. Cross tab to put it back together, grouped by ID

2025-01-10_09-03-59.png

kwilda
6 - Meteoroid

Many thanks Carolyn! I had not considered the transpose, instead fixating on using the dynamic rename.

 

I greatly appreciate the effort

Labels
Top Solution Authors