1 Reply Latest reply: Feb 28, 2017 6:36 AM by Marcus Sommer RSS

    Updating Table Values after a Cross Table load

    Helene Rao

      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

        • Re: Updating Table Values after a Cross Table load
          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