I would have split your preferences on one table for each question. In this case. One ShapePreference dimension (If it is possible that a person may have more than one favorite shape) and one ColorPreference dimension.
With this solution you may calculate how many colors each person prefer. how many persons prefere a color etc.
I provide a qvw-example.
Further to the note I just added, I've just taken a look at the Subfunction field and I'm not sure how I could alter it to suit the data, but you might know a way?
My understanding is that if I used the subfuntion field, I would effectively split out the favourite colours into 3 columns, the problem would then be bringing them all back together so that I could then filter for example 'blue' and show all respondents with blue listed (whether they put it first, second or third)
Not sure if anyone can help here?
If you use the subfield function with only two parameters, i.e. fieldname and separator, then a new record will be created for every value in the list. That also means that it doesn't get confused if there are two, four or ten (comma) separated values. Every value in the list will end up in its own record but in the same column. Just like the result table example you posted.
subfield([Favourite Colours],',') as [Favourite Colours],
Also your qlikview document will not explode in size (unless you have exceptionally large number of records to begin with). See this blog post for an explanation:Symbol Tables and Bit-Stuffed Pointers
Really helpful, this is working - there is a slight issues though which is that qlikview is splitting out the field correctly, but for most fields it is adding a duplicate entry (in a list box for example) with "_x00D_"
Is this a common issue?
Massive thanks for everyones help so far, very nearly there (thanks for your patience)
I've since gone back to the model where I've used the subfield function and realised there is a knock-on impact upon all of my other fields because it effectively spilts out data into multiple rows (as you said G Wassenaar). This is throwing out all of my statistics boxes and pretty much all of my data. is there any way around this??