Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

how to transpose or cross tab for every sheet.

luo
8 - Asteroid

Hi Guys,

 

I am working on a project. I have reached a final stage. Now I have a data table looks like this:

luo_0-1590399794616.png

 

 

After this, I use output data tool spilt the data based on month into several sheets and an example sheet will be similar to the below with sheet name has been named as "Month, YYYY"

 

luo_1-1590399962656.png

 

But I will need something like this:

luo_2-1590400053573.png

 

Now I know I can get total with sum and I know how to do this if I have only one sheet. But I have multiple sheets and of course, if there is other way that can process the data before I use output data tool spliting the sheet, that would be good too. Thanks a lot guys. I have also upload the these sheet for your information. 

 

6 REPLIES 6
RolandSchubert
16 - Nebula
16 - Nebula

Hi @luo ,

 

you'll need Transpose tool, Cross Tab tool and a Dynamic Rename. I've attached a sample workflow. The Select tool is only needed to change the field for the width of the row labels  in your sample (a, b, c width 1) , so that the "Total" row can be named. 

 

Let me know if it works for you.

 

Best,

 

Roland

luo
8 - Asteroid

Hi @RolandSchubert 

Sorry for not making it more clear. Thank you for the workflow. I got the idea to do this on one sheet. 

I will need do this on multiple sheets.

I have the data table like this:

luo_0-1590402078087.png

 

 

And then I use "Output Data" Tool to output this sheet into several sheets based on the month. Like these two sheets below:

luo_1-1590402162616.png

 

AND

 

luo_3-1590402224974.png

 

I will need to do the operation on both sheets. But I cannot do it one by one because there maybe dozens of sheets to process. Is there any way to do this? Thank you again for your time. I have attached this excel also. 

ponraj
13 - Pulsar

Here is the sample workflow. Hope this is helpful. 

 

June.PNGmay.PNGWorfkflow1.PNGworkflow-1.PNG

luo
8 - Asteroid

Hi, ponraj,

 

This is helpful. But can you tell me what's the meaning of your multiple formular? I saw that there is only two group. Can I Group By "Month"?

luo
8 - Asteroid

and also forgive me, why are you using the macro? why not just use the workflow? bring all the tool outside? Could you help me to understand this?

danilang
19 - Altair
19 - Altair

Hi @luo 

 

Here's another example of how to do this.  It's dynamic in that It will work for any number of months, outputting each of them into its own tab with the monthly total rows 

w.png

The workflow builds up all the required rows including the headers as a row.  There are two different output methods.  The one that's in the main workflow simply outputs the the result as is without writing the real headers.  This gives you a file where all the values are written as strings like this.  Note the Green triangles in the number cells

 

AsString.png

 

The second output uses a batch macro to change the header names to the take the first row of data.  This removes the day names from the columns, so it then uses an auto field to convert the "day" columns to integers.  This needs to be done in the macro, because we need to isolate each months data so the rename will work properly.  The output from this has the cells formatted properly as opposed to all being strings

 

Asinteger.png

 

 

Dan

 

Labels