Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a cross table I have loaded which includes many Questionnaire questions. My load statement looks something like this:
TempXTableAnswers:
CrossTable(Question, Answer,3)
LOAD
SurveyID,
1 as XQuestionAnswerCounter,
0 as XQuestionRightorWrong,
Q1,
Q2,
Q3,
.....Qn
resident SurveyAnswersTable
I would now like to update the column XQuestionRightorWrong with a True/False value or 0/1 to indicate if the answer is correct or not. I was hoping to use an "update" type statement similar to what i would do in SQL
Update SurveyAnswersTable Set XQuestionRightorWrong = True where
(Question = "Q1" and Answer = "XYZ") or (Question="Q2" and Answer = ....)
but when i put this inside my load editor in QlikSense it does not recognize the statement and looking at the help, i cant seem to find a way to update values other than using an if statement inside a load statement. This wont work with a cross table and I have a large number of questions (n=~100) so don't feel i can use nested if statements.
What is the best way to do this kind of manipulation ?
Thanks
Helene
Those fields does only exists after the crosstable-statement is finished. Therefore I suggest the following approach:
TempXTableAnswers:
CrossTable(Question, Answer,2)
LOAD
SurveyID,
1 as XQuestionAnswerCounter,
Q1,
Q2,
Q3,
.....Qn
resident SurveyAnswersTable
MapRightAnswers:
Load * inline [
Question, RightAnswers
Q1, x
Q2, y
...
];
Final:
load
*,
if(Answer =
applymap('MapRightAnswers', Question, '#NV'),
dual('true', 1), dual('false', 0)) as XQuestionRightorWrong
resident TempXTableAnswers;
drop tables TempXTableAnswers;
- Marcus