Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Data fields with multiple entries

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):

NameFavourite ColoursFavourite shape
JamesBlue, Red, Greencircle
PeterRed, Yellow, Whiterectangle
HenryBlue, White, Orangetriangle
GeorgeGreen, Blue, Redrectangle
FredWhite, Yellowrectangle
SteveRedtriangle
JordanBlue, Greencircle

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!

NameFavourite ColoursFavourite shape
JamesBluecircle
JamesRedcircle
JamesGreencircle
PeterRedrectangle
PeterYellowrectangle
PeterWhiterectangle
HenryBluetriangle

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

13 Replies
Not applicable
Author

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

rustyfishbones
Master II
Master II

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

qc13092013.png

I hope that helps

Regards

Alan

Not applicable
Author

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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