Fit(bit) your JSON data in Alteryx
In Inviso we are quite intrigued about getting data from API's using Alteryx, and often we get the data back in JSON format. I just got a new Fitbit Charge HR tracker, and we will use my sleep data as an example (I can imagine I will make a post about accessing the Fitbit API as well).
When using "JSON parse" in Alteryx we get some of the way however there is still a bit of work until we get to a traditional row based view. In the following I will describe how we treat JSON in Alteryx.
What we get from the the API looks like this:
Not very helpful right? When the data is parsed through the "JSON Parser" in Alteryx, it looks a little better, however we are still not there.
To make sure that we only get the minute data I use a filter and then use the "Text to column" tool to split "JSON name" values into parts. The outcome will look like this:
After renaming and taking away irrelevant data it should look something like this:
And now the real magic happens when we use the "Cross Tab" tool to transpose the data. If we look at the relevant data and what we would like to accomplish there are 3 goals:
- We want the distinct JSON_Names to be converted into Column names
- We want the JSON_ValueString pairs to be converted into single rows
- We want to group by Id.
To accomplish this we do the following configuration of the "Cross Tab" tool.
Now we have a dataset, that is ready for your favorite analysis tool - Tableau is mine!