Table

[furlonla]

Hi, 

I have an alteryx output with 337 columns. I want to make the data more user friendly. I need a table for each month of the year, and for each month I have 12 columns that I need (I have attached a snippet of the headings)

Should I split these out into separate tabs or can I create a sheet with multiple tables? Is the table tool the best reporting tool to use in this instance.

Any help would be much appreciated!

Thanks!

[pedrodrfaria]

Hi @furlonla 

My recommendation would be to create a macro to read each period you want. You can use a dynamic select to create the logic to read the column names and select only the month you are looking for and output it and then the macro would go to the following period.

A iterative macro to when Iteration = 1 then it does Jan, when it = 2 then it does Feb...

Thanks,

Pedro.

[Qiu]

@furlonla 

If the column names having some certain patter, we can do a transpose, then use Regex to isolate the Year, month and conrresponding values.

Some sample data would help.

[rodalferreira]

Hey @furlonla 

Why don't you create a column month so you would have your table sort of:

Month      Adj Begin Cap     Adj Difference     %Tot Account ....

Jan                  9999                 88888               0.001

Fev                  9999                 88888               0.001

And so forth...

[furlonla]

Hmm I'm not so sure about this, basically at the moment using a dynamic select tool I have 144 columns (12 columns or each month). Is there anyway to stack the data keeping the column headers in the data, giving me 12 columns? Or perhaps a way to create an output with a tab for each month of the year?

[echuong1]

You should be able to use a transpose to pivot your data. From there, you can derive a Year column, and then pivot your data back with a cross-tab. The Year field can be used to dynamically output to multiple files or sheets.

Example attached. Hope this helps!

[SeanAdams]

Hey @furlonla - if you want to stack columns into rows - there are two options.

The most common is to use the transpose tool - that works super-well, and when used in conjunction with the cross-tab you can do exactly what you're looking for.   Transpose tool's only job in life is to change columns into name-value pair rows.

The other option in smaller circumstances (not really suitable for what you're doing, but listed for completness) is the Arrange tool - most people don't use it, but if you need to move a small number of columns into stacked rows, it can be helpful.