Collecting attributes of a field...

[#1]

Hi,

I have a question:  I'm trying to count the number of distinct values and the number of duplicates within a field within a straight table.  I thought that the expression "COUNT([DISTINCT]field_name)" would count the number of distinct values, but I didn't get a result when I reloaded the data. Nor did I get a result for counting the duplicates within a field as well.

So as an example, if FIELD1 contains the following values:

2

2

3

4

5

5

6

6

then I should have 5 distinct values (2,3,4,5, and 6) and 3 duplicates (3, 5, and 6).

Any help is appreciated.

Thanks,

Torino

[swuehl]

What does

=count(distinct FIELD1)

show in a text box expression?

[#3]

It just gives me a ' - ' sign, no value.

[swuehl]

Strange, maybe a typo in your field name FIELD1? Could you post a small sample (or have a look at my attached)?

[#5]

Here is my expression to count the distinct values:

=count([DISTINCT]NOTICE_CNTL)

This was the expression I tried for count the duplicate values:

=Count({1-$}[DISTINCT]NOTICE_CNTL)

Torino

[swuehl]

I think you need to remove the square brackets around DISTINCT. And to count duplicates, look into what I suggested in my app.

[#7]

Thanks for the help, removing the brackets around DISTINCT (and adding a closed parenthesis) helped get the result.

I'm not getting though the correct number of duplicates using the code from your Qlikview document.  I actually ended up with the same number of distinct as duplicates.  Did I miss something in the expression??

count({<NOTICE_CNTL = {"=count(NOTICE_CNTL)>1"}>}distinct NOTICE_CNTL)

[swuehl]

Maybe all your distinct values have multiple occurences / duplicates?

Could you post your sample application or your input data for that field?

[#9]

Yes each distinct value has duplicates in this case.  I can't send any data, but consider this example, which is more representative of the way my data looks:

FIELD2

6

3

2

2

5

3

6

3

4

5

4

4

3

2

Here, I should come up with 9 duplicate values.  Hopefully this helps.

[swuehl · Accepted solution]

Ok, I think you are looking for something different from what I assumed (the number of values with multiple occurence).

Maybe just

=count(FIELD) - count(distinct FIELD)