Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a problem with some survey data which I'm not sure how to process before I load it into Qlikview.
My data is downloaded from the back end of an online survey where we allow multiple answers to be selected for some of the questions.
The data ends up similarly to the example below (see favourite colours):
Name | Favourite Colours | Favourite shape |
James | Blue, Red, Green | circle |
Peter | Red, Yellow, White | rectangle |
Henry | Blue, White, Orange | triangle |
George | Green, Blue, Red | rectangle |
Fred | White, Yellow | rectangle |
Steve | Red | triangle |
Jordan | Blue, Green | circle |
I'm not sure what the best way to process this data is.
I've heard that a good option might be to do the following... but with several questions allowing multiple answers the final database might end up huge!
Name | Favourite Colours | Favourite shape |
James | Blue | circle |
James | Red | circle |
James | Green | circle |
Peter | Red | rectangle |
Peter | Yellow | rectangle |
Peter | White | rectangle |
Henry | Blue | triangle |
Although this might work in principle... i can still see there being issues when finished, ie any 'count' field would be out...
Also, the survey doesnt specify that they NEED to select 3 answers, only that they can select upto... so I'd end up with different numbers of lines for each entry...
Any help would be really appreciated - I'm pretty stumped with this one.
Andrew
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_"
eg
Blue
Blue_x00D_
Green
Green_x00D_
Red
Red_x00D_
Is this a common issue?
Massive thanks for everyones help so far, very nearly there (thanks for your patience)
Andrew
Hi Andrew,
Not sure why you are getting _x00D_ at the end of some of the fields, but you could use PURGECHAR() Function
to clear these values from the fields
so it would look like this in the script
I hope that helps
Regards
Alan
Hi All,
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??
Andrew
Load the colors into a separate table linked by Name. That will preserve the counts in your main table.
Load
Name,
subfield([Favourite Colours],',') as [Favourite Colours]
-Rob