Multi-step data reshape from JSON API return

[dmitry_berman]

Hi All,

I'm having some trouble reshaping JSON data I've received from an API call. Hoping someone can help. The original data comes in as a dictionary with an ExternalD and then some lists of errors and values. I've gotten it to the point in the attached image & can also easily strip out all the numbers & dots in the "col_name" column. My goal is to get the data with one line per error per ExternalID. I've tried various transpose/pivot combinations without success. I think the issue is that an ExternalID could have N number of errors associated with it. Normally in Python I would take the ExternalID and then loop through the error lists associating each error with the ID but I'm not sure how to do that in Alteryx, maybe another macro (I'm already using an iterative one to call the API)?

[jdunkerley79 · Accepted solution]

Have put together a prototype based on your sample.

Basically:

1. Get externalId and join back to data

2. Split record number from col_name

3. Cross Tab to build output table

4. Select to rename a little

Hopefully enough to get you going

[dmitry_berman]

That works, thanks! The key (that I wasn't getting before) was the join to get the ExternalID onto all the correct rows and then grouping by the second portion of col_name in order to duplicate the rows per error. Appreciate it.