Dynamic Rename using Column Values as Prefix + Crosstab
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Dynamic Processing
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Set up of the transpose tool
Set up of the Cross Tab tool.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hey! Give this a try!
- 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 :)
- Transpose, with Key Columns = ID & Plan Type
- Formula Tool to Concatenate the PlanType & Name columns
- Cross tab to put it back together, grouped by ID
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Many thanks Carolyn! I had not considered the transpose, instead fixating on using the dynamic rename.
I greatly appreciate the effort
