Create a pivot table

Question

Hi

I have a dataset that is over a million rows so I can't export to excel without loosing some of the data. With smaller sets of data I have been able to create a pivot table from the data and I am looking to see if there is a way I could do this in alteryx,

The attached file has a basic example of some data similar to what I have with an example of the pivot table I am looking to create

Create pivot table.xlsx

alexnajm · Accepted Answer

Check out the Crosstab tool! You can group by Origin, Change Column Headers to Dest, and Value would be Price with Sum, Total row, and Total column selected

jrlindem · Accepted Answer

@DB010355

Agree with @alexnajm that there is not a way to do this on the interface like with Excel.  But if you're looking to re-create that "dynamic" experience for whomever is using this workflow, you could consider turning it into an Analytic App (assuming you have Gallery or the end user is also licensed).  By adding Interface Tools, you can prompt the user to designate the Product and then the final table will reflect that decision:

I didn't attach the example workflow since it's meant to be illustrative, and I don't know the rest of the context around your need.  But I hope this sparks some additional thoughts or ideas around how you could recreate a similar user experience.

Cheers, -Jay

JerryCooperCMA · Accepted Answer

Good suggestions from Alex and Jay. Another thing you could try is to output as CSV [*Edited: or better yet, as a SQL or other compatible database format*] then bring the data into Excel using Power Query. That would get you past the million-row limit. Excel menu: Data | From Text/CSV [*Edited: or From Database*] | All Files | Import | Load To | PivotTable Report | Add this data to the Data Model. You can view the data directly in a PivotTable. It might be slow but it's worth a try.