topic Re: Data Model For Survey Data in QlikView

Data Model For Survey Data

Tue, 14 May 2013 07:44:25 GMT

Hi Guys

I have to prepare a data model for survey data in which we have a excel file which contains all responses from user, question details and possible response values.

There are single response and multi response questions in survey

Data :

user_id	que_id	subque_id	response
u1	q1		1
u1	q2	1	0
u1	q2	2	1
u2	q1		2
u2	q2	1	0
u2	q2	2	1

Var_Info :

q_id	subque_id	desc	subdesc
q1		What is your Gender
q2	1	What is the age of children	2 years old or younger
q2	2	What is the age of children	Between 3 and 5 years old

Var_Values :

q_id	subque_id	var_value	value_detail
q1		1	Male
q1		2	Female
q2	1	0	Unchecked
q2	1	1	Checked
q2	2	0	Unchecked
q2	2	1	Checked

Now I want to show some statistics in terms of bar graph.

Requirement1: For q1 I want to create a bar graph showing count of users based on gender (gender on x axis and count on y axis).

Requirement2: For q2 I want to create second graph showing count based on subdesc (subdesc on x axis and count of users having response value 1 for every subdesc).

What could be best approach to design this in QV. This is only sample data for two questions.

It would be very helpful to get expert suggestions on this scenario.

//Tribhuwan

Re: Data Model For Survey Data

narender123 — Tue, 14 May 2013 08:14:45 GMT

see the attachment.

Thanks.

Narender

Re: Data Model For Survey Data

Tue, 14 May 2013 10:27:40 GMT

Thanks for your response.

I want to make generic as I have list of questions and some questions have more than 10 sub questions.

Its not only Gender that I could write in load script.

//Tribhuwan

Re: Data Model For Survey Data

Tue, 14 May 2013 11:32:28 GMT

You should start thinking about your datamodel. Based on the info you give I'd suggest you create a fact table for all the surveys and users IDs (I'd recommend also storing dates).

You can then create dimension tables for the users and the surveys. Each question will have it's row with the value filled in.