How to Use a JSON API Within Alteryx

[ztriano]

I am trying to pull stock data from an API in JSON format.

When i do this, it comes in the format of 2 columns and 286,000 rows. As you can see below, it lists the headers and the values in the same column and doesn't make it easy to see which values would belong under which headers

I want to get this into a readable format with multiple columns like a normal table format. 

[bbak91]

Hi @ztriano 

You are very close! Here is an article that I put together surrounding some of the API fundamentals: https://community.alteryx.com/t5/Engine-Works/APIs-for-Beginners-Integrate-All-the-Systems/ba-p/8074... 

All that you need at this point is a text to columns where you parse the JSON name using a period delimiter. Then you use a crosstab to crosstab the data accordingly. 

[ztriano]

@bbak91 I read the article but i am still confused. I tried the cross tab tool and i got the result below. I got different columns but the correct data isn't in each column (dates are listed across all of the columns)

[bbak91]

I think you may need to split your data into two streams with a filter tool where column names go to true and the data goes to false. Then you can use the crosstab on the data and then use the column names with a Dynamic Rename tool. 

[ztriano]

@bbak91 I did that already also. Maybe it is just easier if i share my workflow and you can see what I am doing wrong. It is probably a really small issue because i feel like i am really close to solving this. Thank you so much for your help!

[bbak91 · Accepted solution]

Updated workflow attached! Should be good to go now.