Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
Hi Michael,
It is difficult to say something without seeing the data itself and its structure.
Regards,
Andrey
Sure thing, check out this data set. Specifically, the exams with the Rev15_FormI
ExamId | ItemName | ItemScore | ExamForm |
4513751 | 801SIM20 | 2 | Rev15_FormF |
4513751 | 801SIM1 | 2 | Rev15_FormF |
4513751 | I120701 | 0 | Rev15_FormF |
4513751 | I131584 | 0 | Rev15_FormF |
4513764 | I120702 | 0 | Rev15_FormG |
4513764 | 801SIM20 | 1 | Rev15_FormG |
4513764 | I120887 | 0 | Rev15_FormG |
4513764 | 801SIM1 | 0 | Rev15_FormG |
4513729 | 801SIM1 | 0 | Rev15_FormI |
4513729 | 801SIM26 | 0 | Rev15_FormI |
4513729 | I131584 | 0 | Rev15_FormI |
4513729 | 801SIM6 | 0 | Rev15_FormI |
4513749 | I131020.1 | 1 | Rev15_FormI |
4513749 | I117020.1 | 1 | Rev15_FormI |
4513749 | I131782.1 | 1 | Rev15_FormI |
4513749 | I114476.2 | 1 | Rev15_FormI |
4514633 | I114446.1 | 1 | Rev15_FormI |
4514633 | I120760.2 | 1 | Rev15_FormI |
4514633 | I114150.1 | 0 | Rev15_FormI |
4514633 | I131980.1 | 0 | Rev15_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
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))
Given our data set, that will never happen, but I appreciate the thought. I will try these out. Thanks!
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
Try this:
MAX({$<ItemScoreWeight>{0}>+1<ExamLanguageID={"ENU"}>}Aggr(Count({$<ItemScoreWeight>{0}>+1<ExamLanguageID={"ENU"}>} ItemName), ExamID, ExamForm))