topic Re: Data fields with multiple entries in QlikView

Data fields with multiple entries

Tue, 03 Sep 2013 13:24:44 GMT

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

Re: Data fields with multiple entries

Tue, 03 Sep 2013 13:32:14 GMT

If your concern is regarding the Count function, then you can always use "Count(Distnct 'field')" for getting unique record count.

Re: Data fields with multiple entries

Vegar — Tue, 03 Sep 2013 15:19:38 GMT

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.

Re: Data fields with multiple entries

rajeshvaswani77 — Tue, 03 Sep 2013 15:22:07 GMT

subfield function could be a part of the solution. To be true I am not sure what the full solution will be. I am curious to know the solution.

thanks,

Rajesh Vaswani

Re: Data fields with multiple entries

rustyfishbones — Tue, 03 Sep 2013 15:26:23 GMT

Try using SUBFIELD Function in the Script

So it would be

SUBFIELD([Favourite Colours],',') AS [Favourite Colours]

Regards

Re: Data fields with multiple entries

Vegar — Tue, 03 Sep 2013 15:37:46 GMT

I used the subfield-function in my example.

Re: Data fields with multiple entries

Tue, 03 Sep 2013 17:55:53 GMT

Hi Alan, I think I like the way you're going here.

Sorry to be a complete novice, but could you give a brief overview of how the function works?

Kind regards,

Andrew

Re: Data fields with multiple entries

Tue, 03 Sep 2013 17:56:52 GMT

Thanks for taking the time to do this, really useful, great solution thanks very much!

Re: Data fields with multiple entries

Tue, 03 Sep 2013 18:18:41 GMT

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?

Many thanks,

Andrew

Re: Re: Data fields with multiple entries

Gysbert_Wassenaar — Tue, 03 Sep 2013 18:30:53 GMT

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.

SurveyData:

Load

Name,

subfield([Favourite Colours],',') as [Favourite Colours],

[Favourite shape]

From ...mysource...;

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

Re: Data fields with multiple entries

Tue, 03 Sep 2013 18:57:28 GMT

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_"

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

Re: Data fields with multiple entries

rustyfishbones — Tue, 03 Sep 2013 19:36:52 GMT

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

Re: Data fields with multiple entries

Fri, 06 Sep 2013 14:28:46 GMT

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

Re: Data fields with multiple entries

rwunderlich — Fri, 06 Sep 2013 14:40:49 GMT

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