To carry on my first day tracking Alteryx Challenges, here is my challenge 7.  I will hopefully look back on this day in fondness that I actually started to track these challenges.

Here’s the challenge;

This is the intended result;

Step 1

As soon as you see a JSON file you know you want to be using these three tools;

Input Data > Download > JSON Parse. 90% of the time. That URL we saw in the first image will be downloaded under DownloadData. From there, attach your JSON Parse and it will give us the format below. As a side note, it is often helpful to right-click and cache workflow here. This is will stop the workflow downloading from source every time you run.

Ok, it’s a lot to take in but what do we need? We need to find our fields which could be our column names. Try and think of the final output format and it’ll help guide you. In the image above we see column names 1-8 (luckily) so that should sort that idea out.

Now what is our data? This could be it.

If we look closely we see that it’s the second digit which refers to the column names. We know it is sorted the same as Date is 0 in both. We need to find a way to attach a grouping number as well as eventually pivoting the column names to columns and have the relevant data beneath.

Firstly, text to columns with the delimiter on a full stop. I parsed to 7 columns but you only really need 3. Name / Column Group / Row Group

JSON_Name 3 = column name grouper

JSON_Name 4 = row grouper

Ok, great. We now have an idea. We have the grouping mechanisms. We now need to split the data up so we can join them back together in the right format. I use split records to take those 8 column rows and another path to take all the data.

We need to do this as the column grouping field (JSON_Name 3) needs to be connected to (JSON NAME 4) on the other path and not on JSON_Name 3. This is getting confusing name wise  - I’ll rename them in the second path.

We’re nearly there. Join together. So we join our Column Group and our bottom path to the column group (JSON_NAME 3) on the top path. Remember to bring our row group through the join as we will need it later.

Finally, transform that data. Use the row group as our grouping field. Value string (column headers) as headers and Right Value String for our values.

Sort and we did it! A bit of a long one but important to remember. Every time you use JSON files the workflow is somewhat similar to what we have done here. Good luck!