Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

michael_andrews
New Contributor III

Way to sum items where score is one, grouped by a parent element

Hi, I have a pivot table that shows item responses. Item responses are basically answers to questions on an exam, so one exam can have multiple responses. I need to either create a variable, or figure out a way to get the top score per exam, for each item. The way that is calculated right now, is based on the exam catalog entry, and exam form, we count the max number of item responses for that given form and catalog item, and then use that as a max for all the responses within that form and catalog item. The sql would look something like this.

SELECT TOP(100) SUM(COALESCE(CASE WHEN exam_item.item_score = 1 THEN 1 ELSE 0 END, exam_item.item_score)) AS _correct

  ,exam_excat_id

  , exam_form

  FROM exam_item_response

       INNER JOIN exam_item ON exam_item_response.resp_item_id = exam_item.item_id

       INNER JOIN exam ON exam.exam_id = exam_item.item_exam_id

  GROUP BY exam_excat_id, exam_form

Basically then, each item that matches the exam_excat_id and exam_form, I need that max score. Which is essentially the max count of items where the item score was = 1 grouped by their excat id and form

What's the best way to go about this?