Count the Number of Distinct Value in a Field of a Particular Table in a Schema

[#1]

Dear all,

Supposed I have a data model with a star schema. Field A is the common field that links more than one table together. How can I count the number of distinct value in field A in different tables?

Thanks

[#2]

hi

=count(distinct fieldname)

[avinashelite]

Hi Ivy,

To count distinct value try like below:

Count(Distinct filedA) this will give the result.

If you want Distinct count for each table then need to make changes to your script by adding the script below.

Table1:

Load autonumber(filedA) as Identifier_Table1,

fieldA,

*

from ....

Table2:

Load autonumber(filedA) as Identifier_Table2,

fieldA,

*

from ....

Now  for Table1 > Count( Distinct Identifier_Table1)

and for table2> Count( Distinct Identifier_Table2)

Hope this helps you

[#4]

hi

suppose u have 3 table. and field A is common in all three table.

then in first table :      A as A_tab1

then in second table : A as A_tab2

then in third table :     A as A_tab3

now

count(distnict   A_tab1)

count(distnict  A_tab2)

count(distnict    A_tab3)

[maxgro]

tab1:

load

     A,

     A as newfield

     .....

tab2:

load

     A,

     A as field

     .....

count newfield or field

[#6]

In effect, Avinash tells you to make a copy (or in his case using autonumber) of the filedA key in each table, so you can count the distinct values of the key, but also of each copied key.

[#7]

thanks a lot for your help. I have another question. Supposed I have 2 fields, and I want to merge them together so that I can count the number of distinct values in the across the 2 fields. Is there any way to achieve this?

[maxgro]

in script

field1 & '-' & field2 as newfield

in chart

count(distinct field1 & '-' & field2)

[er_mohit]

Hi

Might be go to table viewer where populate your data relation diagram , there you just put a cursor on that Primary key field. it 'll show you