The title is horrible but that's the best I could do. What I have is something like this:
Country Tag Weight
-----------------------
1 1 20
1 2 30
1 3 77
2 1 10
2 2 11
2 3 100
Or, in a human readable form:
Country Tag Weight
-----------------------
USA Rock 20
USA Pop 30
USA Metal 77
Spain Rock 10
Spain Pop 11
Spain Metal 100
Using either SQL (to create a view) or Data Manipulation Tools in Knime, I need to manipulate the data into this form:
Country Rock Pop Metal
----------------------------
USA 20 30 77
Spain 10 11 100
Essentially, Tag entries (unique ones) become the columns and countries (unique ones) become the row id's with the weight values sticking with their country/tag.
I've tried everything I can think of in Knime and no raw SQL query springs to mind. In Knime, I successfully created the structure of the matrix I want (Country x Tag), but I have no idea how to populate the actual Weight values, they're all question marks. My working solution is to simply output the data into a CSV file in the form I want instead of into the database. But that's klunky and annoying to keep in sync. Any ideas?
You're looking for a pivot or cross table. I'm not much into Knime but that's the technique you want to be googling. if Knime doesn't have that function available, you could do worse than to drop that CSV data into MS Excel and pivot it.