Create a pivot table

[DB010355]

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

[alexnajm · Accepted solution]

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

[DB010355]

Thanks

I did try that but I need to be able to filter by the product as I have done in the pivot table. It currently shows the P1 prices but you can change it to the P2 or both

[alexnajm]

You can filter then beforehand with a Filter tool. You will not be able to add a filter function though like you have in your Pivot - Alteryx outputs the data statically.

[jrlindem · Accepted solution]

@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 solution]

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.