Dynamic Rename using Column Values as Prefix + Crosstab

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

Screenshot 2025-01-10 094221.png

Dynamic Processing

Accepted answers

Carolyn

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

Cross Tab and Transpose.yxmd

All comments

martinson

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

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

kwilda.yxmd

Carolyn

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

Cross Tab and Transpose.yxmd

kwilda

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

I greatly appreciate the effort

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703