If you come from an Excel background (you're not alone), you probably have the same problems as I do with pivoting datasets in Alteryx. Or you have never done it before and just don't quite understand Cross Tab and Transpose, the two native Alteryx tools to pivot your data.
Let's start with Transpose (rows to columns):
We want to change this wide data to long, making it easier to perform operations (Sum, Avg, Count, etc.) using the Summarize tool. Each day should be repeated for each value. Let's take a look at the tool configuration:
It's actually straightforward: Key Columns are the ones that will NOT be transposed, Data Columns will be pivoted. Because we want to keep our date field as is, we click it (F1) on the Key Columns window (if you want to transpose everything, don't click anything here).
Our results:
Things to notice:
Key Columns:
- The field we selected remains, but each date was multiplied by the number of fields we transposed, in this case, four (F2, F3, F4, F5). This will always happen, if you have n fields, you will multiply your Key Columns rows by n.
Data Columns:
- Your previous field names will become values on a new Name field. Always, there's no way currently to change this option. So if you want to rename the column, you'll have to use a Select tool.
- Your previous field values will become new transposed values (now in one single column) in a field called Value. This also can't be changed.
Relatively easy, if compared with our next tool: Cross Tab (Columns to Rows)
We want the F1 field values to become the new column headers and the F2-F5 field values to become the new columns values. Let's check the Cross Tab options:
We have a few more options than with Transpose. They're not as intuitive or straightforward. Sadly.
Group data by these values
- We click the field(s) with the values we want to be shown only once per row. It works like a Group By from the Summarize tool. The field we want to group by here is not instantly recognizable. We'll get to this point later.
Change Column Headers
- Here we select the field with the values we want to become our column headers, F1 in our case.
Values for new Columns
- Now we chose which field should become the new values. But wait, in this case we want four different fields (F2-F5), but we can only chose one (and it can't be the field we've chosen in the previous step). That means we can't achieve our goal with only the Cross Tab right now. More on this soon.
Method for Aggregating values
- You have to chose one aggregation method, so our group by can work. The options you'll have depend on the field's data type. Because we have a string here, we get to chose between concatenate, first and last. In our case, as we'll see, it makes no difference.
Separator & Field Size
- Because we're dealing with strings, in case we chose to concatenate them, we need to chose a separator and a string size.
Enough config, let's try it!
Results:
Wait. That's not exactly what we want. We did get our F1 field as column headers, but not all values were distributed as we expected. That happened because we can only select one field to become our new values, but in this case we need to select four of them. Well, we previously learned how to make one column from multiple rows, so, we can, and should, start here also with a Transpose.
Now we have all our values in a single field and can start to pivot it again, now from long to wide. First the tool configuration, then the explanation.
Starting at the second option:
- F1 as Column Headers is the first thing that comes to mind, because that's what we want.
- Value as Values for New Columns is also simple, that's what we to be our new values.
- The Method for Aggregating Values, as mentioned before, won't impact our result in this case, because no values will be aggregated with the options with chosen.
- Now, the best for last. This might be the least intuitive option, at least it was for me. As we learned before, the field we chose in this step will be the one that will not be repeated (therefore Group By). In this case we're currently not interested in our original column headers, and it is the option we have to group by in order to achieve the result we want. You'll notice on our initial table that F2 has all the 'A' values (Market, Product and Type), F3 has the 'B' and so on. If we group by this field, we'll have all of the same letters in a single row.
And that's it! If you want, you can try this challenge yourself!