API download to JSON Parse to Cross Tab only showing first or last record, not all

Question

Hello, I am working with an API that posts data into JSON. I have used the JSON Parse to create key:value pairs with the field. Ultimately I want the json  name to be the column headers, and the values to all fall into rows. When I use Cross Tab I am able to get the correct column headers but I am forced to choose first or last so I am only getting the first or the last record of values.

How can I have the JSON Name values be an array and all values as rows like this?

blended_rankcategorykeywordpage_numpage_urlsearch_enginesearch_volumetime_Null_

39Regular Web Listinghose reel4https://www.graco.com/us/en/vehicle-service/products/service-garage-lube-equipment/hose-reels.htmlGoogle United States (US) (D)148002019011000

39Regular Web Listinghose reels4https://www.graco.com/us/en/vehicle-service/products/service-garage-lube-equipment/hose-reels.htmlGoogle United States (US) (D)148002019011000

Attached is a screenshot of the workflow output before Cross Tab:

2019-08-20 09_56_42-Alteryx Designer x64 - BrightedgeKeywordDetail.yxmc_.png

MichalM · Accepted Answer

@g_ang311

Try assigning something like a record ID to all rows belonging to the same record. You can use a MutiRow Formula to do this. You will then use group by the record ID in the Cross Tab.

joshuaburkhow · Accepted Answer

In order to crosstab and break out the values you need to have (or create) a field that is unique to each of those rows. What you could do is use the Multi-Row formula. You can create a record Id starting with 1 and for each "search_engine" you increase it by one. Then you use that as your group in your crosstab. Make sense?