How do you reverse my table after filtering some columns

[Priyas_4525]

I have uploaded 2 pictures of the data. In the first, i have converted Years into rows and then through Filter, I have removed certain years as per requirement. Now, I was trying to use Cross tab tool to get the data back to same format as in second picture but somehow, It is not happening. Can anyone help me with the same? Thanks!

[Greg_Murray · Accepted solution]

Hi @Priyas_4525,

For sanity's sake, I would add a record ID tool in before the transpose tool and add record ID as a key column in the transpose tool. Then in the crosstab tool Group by record id, region, industry/market, master category and Companies. The column headers should be 'Name' and the column values should be 'values'. Finally the method of aggregation can be any of the three options, I usually pick First.

Let me know if that works.

Greg

[Priyas_4525]

Thanks for the reply.

I am new to Alteryx and trying to explore ways to make my tool dynamic.

I have attached pictures with steps followed but I am not getting the exact data - I guess, using "Concatenate" would result in the same data.. as I have multiple rows of same company.. So I can't take a sum or any other aggregation.. how can i get the same data with some columns eliminated as per user choice?

[Greg_Murray]

As long as you group by the record id in the crosstab, you can use sum. It won't actually perform any summarization because the record ID is unique to each row. 

Greg

[Priyas_4525]

Correct!

Last question,

My year format is "Year_T-4" but after Crosstab tool it gets converted into Column "Year_T_4" due to which my whole workflow is getting affected. Why is this happening any idea?

[Priyas_4525]

any idea of the above query?

[Greg_Murray]

Sorry, I thought I responded. The crosstab tool replaces special characters and spaces with underscores in the headers. The simplest solution is probably to do something like what I have attached. 

[Priyas_4525]

Thank you!

What if another year comes up in new dataset. For instance, Year_T-7?

Would it also get replaced?

if i select "Dynamic or Unknown Fields in Dynamic Rename Tool, will it take extra columns?

[Greg_Murray · Accepted solution]

Yep, just select Dynamic or Unknown Fields.