How do I represent Select Count Distinct in QlikView?

[#1]

Hi

How do I write the equivalent of SELECT COUNT(DISTINCT TestIDs) in QlikView?

I would like to bring in the count of unique numbers in a column from a table and represent it on a chart.  I wrote Count(TestIDs), but the numbers are different from when I run same in SQL.  Also, I can't seem to work out the formula in Qlikview that equates to SELECT COUNT(Distinct TestIDs).

Can anyone please help please.

Thanks

[hic · Accepted solution]

If you want to use the numbers as dimension, e.g. one bar per number in a a bar chart, then you can use the field name "TestIDs" as dimension. No need for a formula. The dimension automatically picks out distinct values.

And if you want to use the number as the measure in the chart, e.g. the height of the bar, then just write Count(distinct TestIDs) as Expression in the chart.

[#3]

Thanks Henric.  Do I need to write Count(TestIDs) or just put TestIDs in the dimension?

If that's the case, how do I get a count of a subset of those numbers?  For instance I have TestIDs, but I have another column that is like a 'flag'.  I want to count the number of TestIDs that have Flag= 'Yes' for instance.

Any ideas on how I can do this?

[hic]

I suspect you want TestIDs as first dimension, and as expression you should probably use Count(TestIDs) or count of some other field. Count will count the number of records where this field is not NULL.

The field "flag" is something that you can display as list box next to the chart. Then you can make your selection (filter) in the "flag" field and see the result in the chart.

An alternative is to use Count(if(Flag='Yes',TestIDs)), but this construction is slow when you have large amounts of data.