On Day 3 of #DashboardWeek we isolated data from the Rick & Morty API. This particular blog post will be focused on the Alteryx workflow rather than putting everything together in Tableau.

The Project

We had to access the data through several API calls which allows access to isolated data for every character: in which episodes they appear as well as several other characteristics (like whether they are dead or alive, where the are found and whether they are male or female). Using the JSON Parse tool, the data was neatly isolated into headers and values columns.

It is clear that the data will need to be transposed from rows to columns so that each demographic has its own column. But first, some data cleaning using filters, and some awesome RegEx to give me the data in two neat columns. All that remains necessary is to transpose the data so that each demographic is listed in a separate column.

Looking back, I can’t think what I was thinking trying to apply the Cross Tab tool to the data. I set the tool up so that the Heading value was selected to group the data by, but it was also selected as the headings for the columns. The values column was selected as values for the new columns. The result?

Obviously, it is not what I wanted! After trying several different options which further showed that I didn’t have a clue how to use the tool. Only after asking for help, the solution became clear: don’t take out all the unique fields so that possible for the tool to group the data! Thus, going back and creating a unique ID for each character which the tool can use to group the data by, the data was transposed in the correct way.

Setting up the transpose tool so that it groups by Field 1 and the Unique ID and then using Heading as Field names while the Value column is used as the values for each of the columns. Running the Cross-Tab tool now, will give you the wanted results.

In Summary

Don’t try to apply the Cross-Tab tool to only two Fields and then grouping by one of them. A lesson I will never forget!