Can't get proper unique ID count

[michael_andrews]

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

[sunny_talwar]

Do you have ExamID in different tables? Can one of those tables have more than 350 distinct ExamIDs?

[michael_andrews]

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.

[vinieme12]

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

[vinieme12]

Formid>> relates to response Id>> relates to examid

Is this how you are associating the data? Then we wil need  to change it

[sunny_talwar]

Distinct count should not be a problem....

Count or Count distinct?

[michael_andrews]

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.

[michael_andrews]

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.

[sunny_talwar · Accepted solution]

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?

[michael_andrews]

You are a life saver. Thanks! That worked.