Qlik Community

Qlik Sense Integration, Extensions, & APIs

Discussion board where members can learn more about Integration, Extensions and API’s for Qlik Sense.

michael_andrews
New Contributor III

Count Distinct If value Matches current item

So here is my scenario. We have exam data. Each exam record has a form foreign key. We also have exam question data, where each row is basically one question, and it is tied to the exam via a foreign key to the exam id. What I need to do, is count unique questions based on the item name, however, I only need to do it once per exam form.

What I'm getting at is you may have 5 different exam records all using the same form, and I just need to count the unique question records for that exam, for one form. The sql that handles this would be

SELECT COUNT(DISTINCT item_name)

  FROM exam

  INNER JOIN exam_item ON item_exam_id = exam_id

  GROUP BY exam_form

The problem I'm having is it is counting unique item names for all the exams within that form, and it's giving me too big of a number. The reason is, is because that even though they share the same form, sometimes, the questions have different names. However, it does not matter because as long as we count just one exam record, we get the proper number of questions, as they always have the same number of questions, even if they're different names.

How would I go about getting the unique item names for just one exam record per form? This is what the data output looks like in our view. I've tried Count(distinct ItemName) but that's giving me the wrong count. I think I may need to use a set expression? If I am, I'm just not quite clear on how to tell it, I need you to count all the unique questions (ItemName) in just the first exam record you find, that is using this current form (Rev15_FormE, Rev15_FormD) etc.

See my image for a better descritpion

items.png

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Count Distinct If value Matches current item

Is it possible that one of the ExamId had 3 exam while other had 4? How would you handle such a situation? If that can never happen, you can try this:

Only(Aggr(Count(ItemName), ExamId, ExamForm))

or

Max(Aggr(Count(ItemName), ExamId, ExamForm))

6 Replies
ahaahaaha
Honored Contributor

Re: Count Distinct If value Matches current item

Hi Michael,

It is difficult to say something without seeing the data itself and its structure.

Regards,

Andrey

michael_andrews
New Contributor III

Re: Count Distinct If value Matches current item

Sure thing, check out this data set. Specifically, the exams with the Rev15_FormI

    

ExamIdItemNameItemScoreExamForm
4513751801SIM202Rev15_FormF
4513751801SIM12Rev15_FormF
4513751I1207010Rev15_FormF
4513751I1315840Rev15_FormF
4513764I1207020Rev15_FormG
4513764801SIM201Rev15_FormG
4513764I1208870Rev15_FormG
4513764801SIM10Rev15_FormG
4513729801SIM10Rev15_FormI
4513729801SIM260Rev15_FormI
4513729I1315840Rev15_FormI
4513729801SIM60Rev15_FormI
4513749I131020.11Rev15_FormI
4513749I117020.11Rev15_FormI
4513749I131782.11Rev15_FormI
4513749I114476.21Rev15_FormI
4514633I114446.11Rev15_FormI
4514633I120760.21Rev15_FormI
4514633I114150.10Rev15_FormI
4514633I131980.10Rev15_FormI

As you can see, based on the id, there are three different exams with the Rev15_FormI form. While each item name is different, they all have unique items per exam. If I were to just do a count of unique item names where the form is Rev15_FormI, then I would get 12, because there are 3 exams, each with 4 unique item names. However, I only care about the count for one exam in that form. I.e. if I were to do a count where exam id is 4513729 and the Form is Rev15_FormI, I would get 4, and that is the count I want to display for ALL exams with that form. See this image to point out what I'm trying to get at. http://i.imgur.com/a0Z5Kr1.png

MVP
MVP

Re: Count Distinct If value Matches current item

Is it possible that one of the ExamId had 3 exam while other had 4? How would you handle such a situation? If that can never happen, you can try this:

Only(Aggr(Count(ItemName), ExamId, ExamForm))

or

Max(Aggr(Count(ItemName), ExamId, ExamForm))

michael_andrews
New Contributor III

Re: Count Distinct If value Matches current item

Given our data set, that will never happen, but I appreciate the thought. I will try these out. Thanks!

michael_andrews
New Contributor III

Re: Count Distinct If value Matches current item

So I think you have me on the right track. However, I need to add some constraints to this count. I tried using a set expression, but it just gives me nothing when I add it. Here's what I have

MAX(Aggr(Count({$<ItemScoreWeight>{0}>+1<ExamLanguageID={"ENU"}>} ItemName), ExamID, ExamForm))

Basically, what I need to say is only count ones where ItemScoreWeight > 0 AND ExamLanguageID = ENU

MVP
MVP

Re: Count Distinct If value Matches current item

Try this:

MAX({$<ItemScoreWeight>{0}>+1<ExamLanguageID={"ENU"}>}Aggr(Count({$<ItemScoreWeight>{0}>+1<ExamLanguageID={"ENU"}>} ItemName), ExamID, ExamForm))