Unable to pivot data and rename the header

[rajvivan11]

Hi Alterxy Gurus

This is the first time I am posting a question here so apology in advance if I am making any mistake and missed any community rules

Requirement :

I am having two Data set which I am using to get the desired header

First Dataset - Actual Data

Second Dataset- Mapping Table

My first Dataset is having incorrect Hierarchy heading which I need to modify based on my mapping Table but I couldnt able to get the expected output. Can someone please help me with your expertise.

Attaching the sample data for your reference.

Note : (Office Environment )I am using the Alteryx 2018.3

           (Personal PC)I am using the Alteryx 2019.3

FYI.. I was able to get my desired output but the workflow is not effective as per my understanding. In my real time scenario, I have roughly around 70 columns  and data-set has 40 million rows of data which is taking quite a long time to execute the workflow.

Thanks in Advance,

Rajeev

[MichalM · Accepted solution]

@rajvivan11 

This is how I would approach it. I have

• Used another input which I use as a template defining the desired order of columns
• Changed the Lookup table slightly so I can get both ID and Description into the same column

Workflow attached. Please let me know if anything's unclear.

[rajvivan11]

This is really a Clever way to solve this issue .Thanks for sharing your approach.

Will mark you answer correct as soon as I test with my original data

Really loved the below workflow which you used ...Never thought about this and I realized now this approach will solve my other problems as well

I am looking for some more approach ..not only from the point of solving but also wanted to learn new approach techniques

Note : Just FYI..Template is not required actually because you already achieved the output when you transposed it .Select tool will do the rest of the magic

Thanks again.

[MichalM]

Happy to help!

The reason I use the template is that the Cross Tab tool will always sort the columns alphabetically and replace all punctuation and spaces with "_". Hence the template to bring the columns to the correct order and Dynamic rename to replace the underscores.

[rajvivan11]

I am.curious to know why didn't you end up with select tool which can do
both the work - dynamic renaming and Sorting ? #Justathought

[MichalM]

Select requires you to change the column names manually while dynamic rename allows you to define a rule and apply it across all columns just like in this case - remove "_" from all column headers and replace them with a space.

The same goes for order. I'd need to define this manually in the select tool. Appending the data to a "template" using the Union tool will do the job for me automatically.