Loading survey questions and responses into Qlikview

[Anonymous]

I have three excel files which store responses to a number of questions.

Response: It stores some basic information (e.g. gender, ID, school, etc) and respondents' responses to different questions.

Question: It stores the ID of a question and the full text of the question.

Question_option: It stores the question ID, option value, and the text of that option.

So for example, if a respondent answered "1" in Q1, by referring to Question and Question_option, we can know that "1" means "Very poor".

How can I load the data into Qlikview so that it can map responses and option text automatically? I've got more than a hundred questions what is the quickest way to do this? 

[sunny_talwar · Accepted solution]

This??

I have rounded up the responses to handle decimal Responses. Not sure if this how you want it to be handled.

Try the script:

Temp:

CrossTable(QID, Responses, 4)

LOAD ID,

    Gender,

    School_r,

    Program,

    Q1,

    Q2,

    Q3,

    Q4,

    Q5

FROM

Response.xlsx

(ooxml, embedded labels, table is response);

Responses:

LOAD ID,

  Gender,

  School_r,

  Program,

  QID,

  Responses as R,

  Ceil(Responses) as Responses

Resident Temp;

DROP Table Temp;

Left Join(Responses)

LOAD QID,

    Option_Value as Responses,

    Option_Text,

    Is_Excluded

FROM

Question_option.xlsx

(ooxml, embedded labels, table is Question_option);

Left Join(Responses)

LOAD QID,

    Question_Text

FROM

Question.xlsx

(ooxml, embedded labels, table is Question);

Data Model:

[Anonymous]

That's pretty much what I need! The crosstable load is a very powerful function. Thanks.

[sunny_talwar]

I agree, crosstable proves very useful