Alteryx Designer Desktop Discussions

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

macro to pivot data

poojitha4
8 - Asteroid

Hi everyone,

 

I have to create pivoted data from a excel sheet, I have columns like 

Market WeekCompetitor 12345678910111213141516
AWeek 1123                
BWeek 2236                
AWeek 3256                
BWeek 4456                
AWeek 5856                
BWeek 6258                

 

So I have three dimensions which are week, Market and competitor. Columns 1-16 are different metrics. I need to pivot this data by the week. So the new column names will essentially will be Competitor_Market_1, Competitor_Market_2, Competitor_Market_3,Competitor_Market_4,....Competitor_Market_16. I have 2 different markets and 10 different competitors, and 16 different metrics, so essentially I need to have 2*10*16 columns.

 

I am aware that "cross tab" node is useful here, but my only concern is I have to do it for all the 16 metrics and that makes this workflow very bulky.

I want to change this into a macro and I think that is what macros are for, make bulky workflows look easy and simple. I'm not entirely sure on how. I attach a sample image of what I tried to do with the workflow.

I want the part where I use the 16 cross tabs to become a macro, any insights will be helpful.

 

Thank you,

Poojitha.

4 REPLIES 4
cmcclellan
13 - Pulsar

Can you supply some sample data in XLS or CSV format ?

 

I looked at the image and first thought that you wanted to transpose, but reading your post I don't quite understand why you want to do the cross-tab and merge.

 

 

Anyway, I'd be tempted to try with a single cross-tab tool and maybe some filtering after that.

 

Why do you want to do this though ?   I think I'd be wanting to transpose the 1-16 columns though.

poojitha4
8 - Asteroid

Hi @cmcclellan,

I have attached a sample xslx book which has sample input and output sheets. I have this requirement(client requirement) which I had to meet and I did, I just want to push through and find an optimal solution. Any insights?

 

Thank you,

Poojitha

 

john_miller9
11 - Bolide

@poojitha4 I'm not sure that a macro is needed as it looks like the workflow below will accomplish what you've outlined.  If there is something I misunderstood, please let me know.  Thanks.

Macro to Pivot Data.PNG

poojitha4
8 - Asteroid

@john_miller9, you are absolutely right my friend! That's is all I need. Silly me. Thank you so much.

Labels