Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Updating Table Values after a Cross Table load

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

1 Reply
marcus_sommer

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