Using Running sum to Pivot in Alteryx

When pulling data from an API or web-scraping, it is quite common to need to pivot the data at some point. Here's what the data from the Rick and Morty API looked like when I tried to use it today:

All of the data is in one column, which means we need to pivot for it to be easily useable. However, for a pivot we also need to group the data correctly. In this case I wanted every row to be the data for a single character, so I wanted to have a unique id per character on a separate column. However, when you try pull out the given id in this format you get this:

We were only able to get the id on one of the rows so far. We now need to replace the 0's in the new column with that id. This can be quite easily done with a Multi-row tool. However, I really dislike using that tool. The workaround I found was to use a running total instead. Because the data is sorted properly and there are only id's on the first row for each character, each character will still have a unique number assigned to them which can act as an "id" to pivot with. Here's what it looks like after:

Now we can quickly pivot with the JSON_Name column as column names, and JSON_ValueString as the values for those columns and have it grouped by the running total column. After the pivot the data is finally approaching a useable format:

We still retain the correct id's after the pivot, and we can remove the fake id's we assigned.

Author:
Sakif Chowdhury
Powered by The Information Lab
1st Floor, 25 Watling Street, London, EC4M 9BR
Subscribe
to our Newsletter
Get the lastest news about The Data School and application tips
Subscribe now
© 2025 The Information Lab