I just answered this question on the Tableau Community Forums : https://community.tableau.com/message/1082243
It’s the typical problem of having the data created as a cross-tab (ie reporting style rather than database style), but with the added complexity of having 2 dimensions combined together (Country & Year in this case):
I decided to NOT use Alteryx for this one, although the solution would be just as simple 🙂
I created this in Tableau Prep Builder in a few clicks:
I copied the data out of the forum post, the values were separated by tabs, so I called it data2pivot.tsv (because it’s not a CSV right ?)
So the steps are:
- connect to the data: Prep Builder detects it’s a tsv file and sets up everything properly
- pivot the 5 columns (except for Week)
- Add a Step
- Prep Builder recommended that I split the values (awesome, I was intending to write some code to do that):
- But it defaults the Year datatype to numbers. I don’t like seeing my years with commas in the middle (aka 2,020) so I changed the datatype to String (aka Abc)
- Rename the generated columns to Country and Year respectively
- Remove the original field (before the split)
- Rename the other pivotted field to Score
- Output the result to TDE, Hyper or CSV
Open the output in Tableau Desktop and a few more clicks gets you this:
So with a few minutes work we have the “not built for Tableau” data in the format it should be (3 separate dimensions and a measure) and a graph that the user can filter and format as required.
TECH DEBT:
- I’ve built this to handle 5 pivotted fields only. Adding more fields means you need to change the pivot step so that the new fields are pivotted as well.