Unpivot / Reverse Pivot data

[Chris_W]

Hi there, 

I am wondering how I Unpivot data. What I mean, is I would like this: 

Name	Count
A	2
B	1
C	3

to be transformed into this:

Name	Count
A	1
A	1
B	1
C	1
C	1
C	1

Thanks!

[Philip · Accepted solution]

A batch macro with the Generate Rows tool will do this. 

[Chris_W]

Hey Philip,

This works a treat. Thanks so much!

Very green follow up questions if you have the time (sorry - first day using alteryx).

• How do I save this macro so I can use it in my workflow?

[Philip]

No apology needed. We were all beginners at some point.

You should set up a central repository for macros and save it there. Then, you can make all your macros available for workflows. Here is a step-by-step process.

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Macro-Installation/ta-p/13032

This way you'll have a tool folder at the top like the Input, Preparation, etc. and you can just drag and drop them into your workflow.

Let me know if you have additional questions or have any problelms.

[Chris_W]

Haha - thanks.

So I have managed to get as far as you have mentioned with a bit of guidance from your link and YouTube, but am now at another roadblock. How to I get it to run?

Below is my workflow. You can see that I have a bunch of lat/longs and visits in my data source. I then have a select tool to convert my visits to an int32. But am unsure how to use this input in conjunction with your macro? Appreciate all the help immensely. Great first community experience!

[Chris_W]

Oh wait, think I have it. The questions tab in the macro was empty and that is where I map the multipliers. 

~600k rows of data in my input file and after 5 minutes, I'm still at 0%. Think this is going to take a while...

[Philip]

When you link the inputs, there are two tabs that need to be defined. First, the Group By tab is the grouping field, which is Location Input (ever13). Second, the Questions tab will ask for the group by field, which is Location Input (ever13) and then the iterations field, which is the All Visits field.

[Philip]

Saw your reply after I had posted. Glad you figured it out!

I'll be interested in knowing how long it takes to run for that many records.

[Chris_W]

Tripped up on the grouping tab (was seeing billions of records with 0% progress after leaving it running overnight), but now have got everything sorted. Success! 

Final run time is at 7 minutes, total fine for a one off process, converting 402K records to my expected 3.1 million records.

Thanks again for all your help Philip - loving this tool!