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

[g_ang311]

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_rank	category	keyword	page_num	page_url	search_engine	search_volume	time	_Null_
39	Regular Web Listing	hose reel	4	https://www.graco.com/us/en/vehicle-service/products/service-garage-lube-equipment/hose-reels.html	Google United States (US) (D)	14800	201901	1000
39	Regular Web Listing	hose reels	4	https://www.graco.com/us/en/vehicle-service/products/service-garage-lube-equipment/hose-reels.html	Google United States (US) (D)	14800	201901	1000

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

[MichalM · Accepted solution]

@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.

[geraldo]

Hi,

I believe json's record comes in one line. So just put RecordId after reading the file before the json tool. And after that use CrossTab tool by RecordId.

[]

[joshuaburkhow · Accepted solution]

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?