topic Re: Can't get proper unique ID count in Integration, Extension & APIs

Can't get proper unique ID count

michael_andrews — Wed, 08 Feb 2017 19:51:15 GMT

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

Re: Can't get proper unique ID count

sunny_talwar — Wed, 08 Feb 2017 20:13:08 GMT

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

Re: Can't get proper unique ID count

michael_andrews — Wed, 08 Feb 2017 20:27:39 GMT

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.

Re: Can't get proper unique ID count

vinieme12 — Wed, 08 Feb 2017 20:28:38 GMT

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

Re: Can't get proper unique ID count

vinieme12 — Wed, 08 Feb 2017 20:31:18 GMT

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

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

Re: Can't get proper unique ID count

sunny_talwar — Wed, 08 Feb 2017 20:32:47 GMT

Distinct count should not be a problem....

Count or Count distinct?

Re: Can't get proper unique ID count

michael_andrews — Wed, 08 Feb 2017 20:52:39 GMT

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.

Re: Can't get proper unique ID count

michael_andrews — Wed, 08 Feb 2017 21:09:06 GMT

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.

Re: Can't get proper unique ID count

sunny_talwar — Wed, 08 Feb 2017 22:39:19 GMT

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?

Re: Can't get proper unique ID count

michael_andrews — Wed, 08 Feb 2017 23:02:35 GMT

You are a life saver. Thanks! That worked.

Re: Can't get proper unique ID count

sunny_talwar — Wed, 08 Feb 2017 23:04:31 GMT

Awesome

Please close this thread down by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Re: Can't get proper unique ID count

JL1996 — Fri, 27 Nov 2020 06:28:00 GMT

hello Sunny,

it sounds like you are an expert in Qlik Sense. Do you mind to take a look at my question when you are available? thanks