9 Replies Latest reply: Aug 16, 2017 7:35 AM by Antonio Mancini RSS

    Exceptions in a set expression

    Mohammed Al Radi

      Dear Qlik Community,

       

      I am working on a dataset that consists of three columns:

       

      1)name

       

      2) question

       

      3) answer

       

      The answers are all either true and false. I am trying to create a master measure that measures % correct answers. The difficulty in this is that the correct answer differs for each question...some are 'false and others 'true', so I cannot simply instruct Qlik to do for example:

       

      Count ({$<answer = {'true'})>}[answer])/ Count ([answer])  and treat the percentage produced  as 'correct' because not all correct answers should be answered 'true'.

       

      I would need to instruct Qlik to count true as correct for some questions and false as correct for other questions. There are 5 questions under the question field. Based on the three fields provided above (name, question, answer) how could I make an expression that takes this into account.

       

      For example, lets say I wanted to amend the above expression and within the first segment (the set expression) instruct qlik to count :

       

      answers for question 1 that are false

       

      answers for Question 2 that are true


      answers for Question 3 that are false

       

      Answers for Question 4 that are true

       

      Answers for Question 5 that ae true


      because those aforementioned answers are correct. Would it be some really long if statement within the set expression? If so would you be able to provide a rough example? Are there other ways of doing this? Perhaps through the dataload editor? Some sort of inline table?

       

      Apologies I am new to this! Thank you for your support.

       

      Best,

       

      Mohammed

        • Re: Exceptions in a set expression
          Tomasz Truszkowski

          Is it possible to create flag for correct answers in the data model?

            • Re: Exceptions in a set expression
              Mohammed Al Radi

              Hi Tomasz,

               

              Thank you for your reply. I have never created a flag before for correct answers. What function would that entail?

               

              Best,

               

              MOhammed

                • Re: Exceptions in a set expression
                  uri ophir

                  Hi, to create a flag which flags the right answer to each question, add to the LOAD script:

                   

                  Table:

                  LOAD

                       If((Question = 1 AND Answer = 'false')  

                            OR (Question = 2 AND Answer = 'true')

                                 OR (Question = 3 AND Answer = 'false')  

                                      OR (Question = 4 AND Answer = 'true')  

                                           OR (Question = 5 AND Answer = 'true'), 1, 0) AS CurrectAnswerFlag

                  FROM [*.qvd]


                  Now you can write the following expression which divides the right answers (Flag = 1) by the total aswers:


                  Count ({$< CurrectAnswerFlag = {'1'})>}[answer])/ Count ([answer])

                    • Re: Exceptions in a set expression
                      Mohammed Al Radi

                      Hi Uri,

                       

                      Thank you for that.

                       

                      I have tried to recreate the above in my load script (within the existing table and then as a new table) but on luck with both. I believe I am getting the syntax wrong.   Is there anyway other way you would write it? Thank you.

                       

                      Within Existing Table:

                       

                      [Original Table]:

                      LOAD

                      [Name],

                      [Question],

                      if (([Question] = 'Question1' AND [Answer]= '1')

                      OR([Question] = 'Question2' AND [Answer] = '2')

                      OR ([Question] = 'Question3' AND [Answer] = '1') as correctanswer

                      [Answer]

                      FROM [lib://Desktop/testcurrentflag.xlsx]

                      (ooxml, embedded labels, table is Sheet1);

                       

                      As a new Table:


                       

                      [Original Table]:

                      LOAD

                      [Name],

                      [Question],

                      [Answer]

                      FROM [lib://Desktop/testcurrentflag.xlsx]

                      (ooxml, embedded labels, table is Sheet1);

                       

                      [AnswerFlagtable]:

                      Load *,

                      if (([Question] = 'Question1' AND [Answer]= '1')

                      OR([Question] = 'Question2' AND [Answer] = '2')

                      OR ([Question] = 'Question3' AND [Answer] = '1') as correctanswer

                      Resident [Original Table];

                      Drop table [Original Table];

                       

                • Re: Exceptions in a set expression
                  Antonio Mancini

                  Hi,

                  what about

                  (Count({<Question={1},answer={'false'}>} answer)+Count({<Question={2},answer={'true'}>} answer)+.......)

                  /Count(ansver)

                  Regards,

                  Antonio