Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
That works! Thanks.