Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have pivot table and I'm trying to count unique ids vs a form. However, my count is not coming out as expected. Can someone tell me what I'm missing? I'm using the formula Count(distinct ExamID)
See attached for what my pivot table looks like.
The sql equivalent would be SELECT COUNT(1) FROM Exam WHERE Exam_Form = 'Rev15_FormD' GROUP BY ExamID
Try this:
Count(DISTINCT {<ExamID = {"=Count(ItemID) > 0"}>} ExamID)
This will only could those ExamID where the Count of ItemID is greater than 0. Is that what you want?
Do you have ExamID in different tables? Can one of those tables have more than 350 distinct ExamIDs?
No, the exam ids would be in the same table as the form name. The table absolutely could have more than that due to there being multiple forms. I guess I was i'm trying to do is say, for that column under the Rev15_FormD, I know for a fact there are 350 unique IDs, but I'm getting the total number of records.
The reason they're high is because each exam has multiple responses associated with it.
Do not count keyfields
Qlikview can't decide field from which table to count on...
If exam I'd is a keyfield and you want to count on the same field
Load another column with examI'd with a different alias specific to that Table
Example:
Table1 can have examid, examid as examid_t1
Table 2 can have examid,examid as examid_t2
Both your tables are associated and you can choose to count values from table 1 ro table 2 using the alias fields
Formid>> relates to response Id>> relates to examid
Is this how you are associating the data? Then we wil need to change it
Distinct count should not be a problem....
The exam table has exam id which would be a PK. The exam table also has just a text field called exam_form, which are varchars, but do match up. There is then an item response table where each item response has a FK to exam id.
Think of it in terms of taking an exam (the exam table) and the questions you get are in a different order or different questions from the person next to you (the exam form) and then each question you answer would be it's own record (item response) and that would link to your exam.
However, I think I might have something wrong with my data, because when I hard filter on say, 10 exams, I do get a count of 10 as expected. So I'm going to investigate further. Thanks for your help though, it got me headed in the right direction.
Ok, so I think I have found my problem, I'm just not quite sure how I handle this. The issue I'm running into is, we have exams in our data set that have 0 responses. So, I need to only count exams where there is at least one response. I.e.
SELECT * FROM exam WHERE exam_form = 'Rev15_formD' returns the 2549 number I was getting, but
SELECT * FROM exam
INNER JOIN exam_item ON item_exam_id = exam_id
WHERE exam_form = 'Rev15_formD'
Gives me the 350 number I'm expecting. So,
Aggr(max(ItemID), ExamId) but that is returning nothing.
Try this:
Count(DISTINCT {<ExamID = {"=Count(ItemID) > 0"}>} ExamID)
This will only could those ExamID where the Count of ItemID is greater than 0. Is that what you want?
You are a life saver. Thanks! That worked.