Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
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
MVP & Luminary
MVP & Luminary

Re: Updating Table Values after a Cross Table load

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