Combine two tables in Qlikview

[Anonymous]

I have a MS Access (sample attached) which stores the responses of a survey. It has two tables:

Table 1: Response - it stores the responses of different questions. Each row represents a response from a respondent.

RID: Key

Q001: Response of Q1

Q002: Response of Q2

And so on...

Table 2: Question_option - it stores the details of different questions' options. Each row represents one question option of a question.

QID: ID of a question.

Option_Value: Numerical value of a question option.

Option_Text: The displayed text of that question option

Is_Excluded: Indicate a question option should be excluded in calculating statistics

I would like to import all data from these two tables into Qlikview and combine them into a single table.

The new table in qlikview should look like this:

RID 

Q001_Value

Q001_Text

Q001_Is_Excluded

Q002_Value

Q002_Text

Q002_Is_Excluded

And so on....

Any ideas on achieving this? Thanks in advance

[#2 · Accepted solution]

Use the cross table to transpose the Response table

Response:

LOAD RID,

    Q001,

    Q002,

    Q003,

    Q004,

    Q005;

SQL SELECT *

FROM Response;

LOAD QID,

    `Option_Value`,

    `Option_Text`,

    `Is_Excluded`;

SQL SELECT *

FROM `Question_option`;

Response_Final:

CrossTable(QID, Values)

Load *

resident Response;

Drop Table Response;

[Anonymous]

That works! Thanks.